How to remove numbers but only if immediately after a non numeric character

[rebweb]

I have a file with an entry for every date of the year. IT contains text and numbers and special characters like quotes, parentheses etc. The problem is that it was taken from a print book with footnotes, so many numbers are embedded in the text right after another character that points to a footnote.

We are not showing footnotes so we need to remove those numbers only. Basically any number immediately preceded by a character should be removed.

I looked all over and did not find a solution. Please help.

 

[hagia_sofia]

Hello, it would be helpful if you could post a sample of the data so that we could see how it is structured and what are some examples the need to be addressed...

 

[rebweb]

Hello, it would be helpful if you could post a sample of the data so that we could see how it is structured and what are some examples the need to be addressed...

It is a full text field.
A resume of the explanation of the third method: U—V’ahavta..., “Love your fellow as yourself.”6 ; as the Alter Rebbe declared, that this love is an instrument, a means to “Love the Eternal your G-d.”7 This is explained in the statement, “Whoever is pleasing to man is pleasing to G-d.”8 This service of teshuva stems from goodness of heart.

​

Hello, it would be helpful if you could post a sample of the data so that we could see how it is structured and what are some examples the need to be addressed...

It's a potentially long text field. Here is one example:

A resume of the explanation of the third method: U—V’ahavta..., “Love your fellow as yourself.”6 ; as the Alter Rebbe declared, that this love is an instrument, a means to “Love the Eternal your G-d.”7 This is explained in the statement, “Whoever is pleasing to man is pleasing to G-d.”8 This service of teshuva stems from goodness of heart.

 

[rebweb]

It is a full text field.
A resume of the explanation of the third method: U—V’ahavta..., “Love your fellow as yourself.”6 ; as the Alter Rebbe declared, that this love is an instrument, a means to “Love the Eternal your G-d.”7 This is explained in the statement, “Whoever is pleasing to man is pleasing to G-d.”8 This service of teshuva stems from goodness of heart.

​

It's a potentially long text field. Here is one example:

A resume of the explanation of the third method: U—V’ahavta..., “Love your fellow as yourself.”6 ; as the Alter Rebbe declared, that this love is an instrument, a means to “Love the Eternal your G-d.”7 This is explained in the statement, “Whoever is pleasing to man is pleasing to G-d.”8 This service of teshuva stems from goodness of heart.

The 6, 7, and 8 in this example should be removed.

 

[rebweb]

Another example:
Birth of R. Shalom Dovber in the year which the Tzemach Tzedek termed Kitra1 (“crown”)—5621 (1860). He was named for the Mitteler Rebbe2 and half the name of the Tzemach Tzedek’s father.3 Every year my father delivered a maamar on his birthday, but he did so secretly except when it coincided with Shabbat. On his last birthday here on earth4 he said the maamar Natata lirei’echa neiss...5 When he concluded he said to me, “on one’s birthday he should say Chassidus. May G-d give you a gift that you may say Chassidus on your birthday, but it should be with kindness and mercy.” It took seven years for this to happen.6

 

[hagia_sofia]

.

 

[hagia_sofia]

Here is an attempt for Excel 365 with some reservations - could you possibly please test it for a feedback?

=LET(
array,TRIM(A1)&" ",
signs,VSTACK(CHAR(SEQUENCE(15,,33)),CHAR(SEQUENCE(69,,58))),
matrix,signs&TRANSPOSE(SEQUENCE(100))&" ",
a,TOCOL(IF(ISNUMBER(FIND(matrix,array)),matrix,NA()),2,TRUE),
b,REDUCE(a,SEQUENCE(10,,0),LAMBDA(x,y,SUBSTITUTE(x,y,""))),
TRIM(TEXTJOIN(b,FALSE,TEXTSPLIT(array,a))))

Where:

- A1 is the text to be selected;
- the VSTACK(CHAR(SEQUENCE(15,,33)),CHAR(SEQUENCE(69,,58))) is supposed to be a list of items that can precede a footnote;
- as the maximum footnote number is unknown, 100 within TRANSPOSE(SEQUENCE(100)) can be altered to match a higher number;
- the formula is based on assumption that a footnote does not start by 0 (it could be adjusted);
- the formula is based on assumption that there is a space behind any footnote.

P. S. Apologies or the post above there was a mistake.

 

[rebweb]

Here is an attempt for Excel 365 with some reservations - could you possibly please test it for a feedback?

=LET(
array,TRIM(A1)&" ",
signs,VSTACK(CHAR(SEQUENCE(15,,33)),CHAR(SEQUENCE(69,,58))),
matrix,signs&TRANSPOSE(SEQUENCE(100))&" ",
a,TOCOL(IF(ISNUMBER(FIND(matrix,array)),matrix,NA()),2,TRUE),
b,REDUCE(a,SEQUENCE(10,,0),LAMBDA(x,y,SUBSTITUTE(x,y,""))),
TRIM(TEXTJOIN(b,FALSE,TEXTSPLIT(array,a))))

Where:

- A1 is the text to be selected;
- the VSTACK(CHAR(SEQUENCE(15,,33)),CHAR(SEQUENCE(69,,58))) is supposed to be a list of items that can precede a footnote;
- as the maximum footnote number is unknown, 100 within TRANSPOSE(SEQUENCE(100)) can be altered to match a higher number;
- the formula is based on assumption that a footnote does not start by 0 (it could be adjusted);
- the formula is based on assumption that there is a space behind any footnote.

P. S. Apologies or the post above there was a mistake.

Thanks. I get a #CALC.

 

[hagia_sofia]

Thanks for the feedback. Just to be sure: if you copy the formula from above (I made one change after it was posted) and test it on the test data above, will it still return #CALC?

 

[alansidman]

Use Power Query and the following Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Select([Column1],{" ","-","a".."z","A".."Z"}))
in
    #"Added Custom"

Book7
	C	D
1	Column1	Custom
2	U—V’ahavta..., “Love your fellow as yourself.”6	UVahavta Love your fellow as yourself
3	“Love the Eternal your G-d.”7	Love the Eternal your G-d
4	“Whoever is pleasing to man is pleasing to G-d.”8	Whoever is pleasing to man is pleasing to G-d
Sheet1