nested substitute()

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
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

[TABLE="width: 500"]
<tbody>[TR]
[TD]address[/TD]
[TD]street name[/TD]
[/TR]
[TR]
[TD]123 main st
[/TD]
[TD]=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C19,1,""),2,""),3,"")[/TD]
[/TR]
[TR]
[TD]1 home ave[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12345 west ave[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]363 east st[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Assuming the numbers are always the first characters and always followed by a space:

Excel Workbook
AB
1addressstreet name
2123 main stmain st
31 home avehome ave
412345 west avewest ave
5363 east steast st
Sheet2
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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.
 
Last edited:
Upvote 0
Very good point. Never thought about numbers in the street name. Thank you all for all help.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top