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.

 

[rebweb]

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?

 

[rebweb]

View attachment 116793

Mixed on some rows vs others

 

[rebweb]

and for the rows it ran, it unfortunately did not work

 

[rebweb]

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

Thanks. I am not sure I know how to do that. But in the result we are losing the good quote marks and the good M-dash. All I really want is the footnote numbers to be removed.

 

[hagia_sofia]

and for the rows it ran, it unfortunately did not work

Coul you possibly upload one example where it did not work and one example where it returns #CALC error?

 

[hagia_sofia]

So, after some observations there is an adjusted formula which should work where it now returns a result:

=LET(
array,TRIM(A1)&" ",
signs,VSTACK(CHAR(SEQUENCE(15,,33)),CHAR(SEQUENCE(69,,58)),CHAR(146),CHAR(148)),
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))))

As far as #CALC errors are concerned some sample text with the error could help.

 

[rebweb]

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?

What I get is some rows have #calc. And some rows have text. The text rows however still have the issue with the footnote.

 

[rebweb]

ג​

תשרי​

01A

3​

^^

^^

The Tzemach Tzedek had yechidus with the Alter Rebbe on Monday of Teitzei, 6 Elul 5564 (1804); the Rebbe told him: On Shabbat Tavo 5528 (1768), my Rebbe (the Maggid of Mezritch) said a “Torah” beginning V’shavta ad Havayeh Elokecha. He explained that the avoda of teshuva must attain a level at which Havayeh, transcendent Divinity beyond worlds, becomes Elokecha—Elokim being numerically equivalent to hateva (nature), and as we find, “in the (Translation continued on next page) beginning Elokim created the heavens and the earth etc.” All the Holy Society (disciples of the Maggid) were profoundly stirred by this teaching. The tzadik R. Meshulam Zusya of Aniponli said that he could not attain the heights of such a teshuva; he would therefore break down teshuva to its components, for each letter of the word teshuva is the initial of a verse: T: Tamim—“Be sincere with the Eternal your G-d.” Sh: Shiviti—“I have set G-d before me always.” U: V’ahavta—“Love your fellow as yourself.” V: B’chol—“In all your ways, know Him.” H: Hatznei’a—“Walk discreetly with your G-d.” When my father told me this, he concluded: The word teshuva comprises five (Hebrew) letters, each letter a path and a method in the avoda of teshuva. (He explained each method at length). Each moves from a potential state to actuality through the avoda of davening.

* * * * * *

^^

^^

^^

^^

^^

***

0​

0​

1​

1​

2​

FALSE​

TRUE​

^^

^^

#CALC!​

ד​

תשרי​

01A

4​

^^

^^

A resume of my father’s explanation of the first method (of teshuva, see above): T—Tamim..., “Be sincere with G-d, your G-d.”1 This represents the avoda of teshuva that comes through sincerity. Sincerity, or “wholeness,” takes a number of forms and has many levels. In reference to teshuva the highest form is wholeness of heart—called “earnestness”; as Torah says of Avraham, “you found his heart faithful2 before You.”3

* * * * * *

^^

^^

^^

^^

^^

***

0​

0​

1​

1​

3​

FALSE​

TRUE​

^^

^^

A resume of my father’s explanation of the first method (of teshuva, see above): T—Tamim..., “Be sincere with G-d, your G-d.”1 This represents the avoda of teshuva that comes through sincerity. Sincerity, or “wholeness,” takes a number of forms and has many levels. In reference to teshuva the highest form is wholeness of heart—called “earnestness”; as Torah says of Avraham, “you found his heart faithful before You.”3

 

[hagia_sofia]

Hello again, many thanks for posting those examples. Based on those it is obvious that there are no footnotes within the 1st one. So, the adjusted formula is as follows:

=LET(
array,TRIM(A1)&" ",
signs,VSTACK(CHAR(SEQUENCE(15,,33)),CHAR(SEQUENCE(69,,58)),CHAR(146),CHAR(148)),
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,""))),
IFERROR(TRIM(TEXTJOIN(b,FALSE,TEXTSPLIT(array,a))),array))

As far as the latter problem is concerned, cf. the attachment which shows the result of the formula above for the 2nd example in my computer. Since it calculates within your computer your version of Excel knows all the necessary functions. After some thinking the only things that came to my mind are that a) you could try to update your Excel or b) the version of Excel you are using is not for Windows but for Mac.

 

[rebweb]

Hello again, many thanks for posting those examples. Based on those it is obvious that there are no footnotes within the 1st one. So, the adjusted formula is as follows:

=LET(
array,TRIM(A1)&" ",
signs,VSTACK(CHAR(SEQUENCE(15,,33)),CHAR(SEQUENCE(69,,58)),CHAR(146),CHAR(148)),
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,""))),
IFERROR(TRIM(TEXTJOIN(b,FALSE,TEXTSPLIT(array,a))),array))

As far as the latter problem is concerned, cf. the attachment which shows the result of the formula above for the 2nd example in my computer. Since it calculates within your computer your version of Excel knows all the necessary functions. After some thinking the only things that came to my mind are that a) you could try to update your Excel or b) the version of Excel you are using is not for Windows but for Mac.

OK I replaced the formula with the updated version and it looks good. I will spend more time on QA but this is amazing. Thank you so much!