nested substitute()

[lezawang]

Hi
I want to get rid of all numbers in a text. So I thought of used nested substitute() function and nested 10 times go get ride of 0-9. I have a column with house address and i want to keep street address not the number and then autofill this function. Do you think there is better idea to do that? thanks

address	street name
123 main st	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C19,1,""),2,""),3,"")
1 home ave
12345 west ave
363 east st

<tbody>
</tbody>

 

[JoeMo]

Assuming the numbers are always the first characters and always followed by a space:

Excel Workbook
	A	B
1	address	street name
2	123 main st	main st
3	1 home ave	home ave
4	12345 west ave	west ave
5	363 east st	east st
Sheet2

 

[lezawang]

Thank you so much. That is great. I just did not understand why LEN(A2) is used. I know it is the 3rd argument of MID() but did you use that to make sure it is covering the whole text. I mean let say I decided to put instead of LEN(A2), I put a 100. What is wrong with that?
Thanks once again for your help

 

[JoeMo]

Thank you so much. That is great. I just did not understand why LEN(A2) is used. I know it is the 3rd argument of MID() but did you use that to make sure it is covering the whole text. I mean let say I decided to put instead of LEN(A2), I put a 100. What is wrong with that?
Thanks once again for your help

You are welcome. 100 would be fine provided you are 100% CERTAIN THAT len(a2) never exceeds 100. Using LEN(A2) makes the formula self-adjust to the length of the string in A2!

 

[Rick Rothstein]

I just did not understand why LEN(A2) is used. I know it is the 3rd argument of MID() but did you use that to make sure it is covering the whole text.

Yes, that is the reason JoeMo used it.

I mean let say I decided to put instead of LEN(A2), I put a 100. What is wrong with that?

There is nothing wrong with doing that so long as you know none of your street addresses will ever be longer than 100 characters.

By the way, as an aside, I see one problem with you original plan to substitute all digits with ""... think about street addresses like this (lots like this in New York City)...

123 W23rd St.

 

[lezawang]

Very good point. Never thought about numbers in the street name. Thank you all for all help.