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.
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
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.
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.
Old | New | New Number |
Dr Bohrgasse 7 | Dr Bohrgasse | 7 |
12 Mahlerstrasse Stiege 5 | 12 Mahlerstrasse Stiege | 5 |
Zieglergasse 28 | Zieglergasse | 28 |
Grunau 11 | Grunau | 11 |
Schubertring 8 | Schubertring | 8 |
Hegelgasse 13-22A | Hegelgasse | 13-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.