Removing all text before a number at the end of a string

Rashie

Board Regular
Joined
Jun 5, 2015
Messages
55
Hey,

Due to a system update something has knackered internally and I'm fretting a little trying to fix it.

I have some addresses, like the examples below.

OldNewNew Number
Dr Bohrgasse 7Dr Bohrgasse
7​
12 Mahlerstrasse Stiege 512 Mahlerstrasse Stiege
5​
Zieglergasse 28Zieglergasse
28​
Grunau 11Grunau
11​
Schubertring 8Schubertring
8​
Hegelgasse 13-22AHegelgasse13-22A

I wish to remove the number just at the end of each string. The number can also include a hyphen, more than 1 number or a suffix. As you can see on line 2 there can also be numbers at the start. I only want the numbers at the end of each line.

The formula should be something like

IF(ISNUMBER(--LEFT(A1,1)),MID(A1,FIND(" ",A1)+1,LEN(A1)),A1)

But this is literally doing the opposite of what I want it to do! It's removing the numbers and leaving the street. Can anybody help?

Thanks,
Rashie.
 
In that case you probably don't have the LET function yet.
In which case how about
+Fluff 1.xlsm
ABC
1OldNewNew Number
2Dr Bohrgasse 7Dr Bohrgasse 7
312 Mahlerstrasse Stiege 512 Mahlerstrasse Stiege 5
4Zieglergasse 28Zieglergasse 28
5Grunau 11Grunau 11
6Schubertring 8Schubertring 8
7Hegelgasse 13-22AHegelgasse 13-22A
8Center MonnaieCenter Monnaie 
Lists
Cell Formulas
RangeFormula
B2:B8B2=IF(ISERROR(MID(A2,FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,1)*1),A2,LEFT(A2,FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))
C2:C8C2=TRIM(SUBSTITUTE(A2,B2,""))
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
This is ridiculously close! Thank you!

Only slight issue is that if there is no number it's just separating the strings

(i.e.
Center MonnaieCenterMonnaie
)

Any way you can think of to ignore this situation?

Thanks again, we're so close and I owe you big time
Hi,
Look like there's a discrepancy between my excel and your excel output. In column C, if there's no number, then I get a #VALUE error on B. Can you confirm, or is it actually separating strings.
 
Upvote 0
@BigBeachBananas In future please post your actual formula to the board, rather than just an image. It saves members from having to type it out & possibly making a mistake.

I get the result as the OP with your formula & also it could fail in some scenarios, as shown in row 9
+Fluff 1.xlsm
ABCDE
1OldNewNew Number
2Dr Bohrgasse 7Dr Bohrgasse 7Dr Bohrgasse7
312 Mahlerstrasse Stiege 512 Mahlerstrasse Stiege 512 Mahlerstrasse Stiege5
4Zieglergasse 28Zieglergasse 28Zieglergasse28
5Grunau 11Grunau 11Grunau11
6Schubertring 8Schubertring 8Schubertring8
7Hegelgasse 13-22AHegelgasse 13-22AHegelgasse13-22A
8Center MonnaieCenter Monnaie CenterMonnaie
95 Mahlerstrasse Stiege 55 Mahlerstrasse Stiege 5#VALUE!5
Lists
Cell Formulas
RangeFormula
B2:B9B2=IF(ISERROR(MID(A2,FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,1)*1),A2,LEFT(A2,FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))
C2:C9C2=TRIM(SUBSTITUTE(A2,B2,""))
D2:D9D2=LEFT(A2,FIND(E2,A2)-2)
E2:E9E2=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))
 
Upvote 0
This looks great but I can't get it to work, it just gives me #NAME? error.... Any ideas?
All fixed and working. For some reason Excel wasn't allowing it.

Thanks everybody in the thread for their help! :D So glad you all exist.

Rash.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
These formulas should work in XL365 if your text is never longer than 100 characters (change the two 100's in the SEQUENCE functions if it could be) and the last "number" is always preceded by a space...

B2: =IFERROR(LEFT(A2,MAX(IF(MID(A2,SEQUENCE(100),1)=" ",SEQUENCE(100)))-1),"")

C2: =TRIM(SUBSTITUTE(A2,B2,""))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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