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

R

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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Hey, Using 365.
I'm making some progress
=TRIM(LEFT(B8,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B8&"0123456789"))-1)) - this is finding all text up to a number
=TRIM(REPLACE(B8,1,LEN(D8),"")) - this is finding just the number

However, in conjunction they don't quite work as it starts separating at any number, I need it to separate just if there's a number at the end

Using my current formulas the below does separate but at the first number, i need it to separate only at the last number.

Leitstelle 9I Waehringer Gue 18-20Leitstelle9I Waehringer Gue 18-20

Sadly I'm dealing with over a million lines and pretty much need it to work first time lol

Thanks again, hope my work so far helps?

Rashie
 
Upvote 0
Try

Book3
ABCD
1OldNewNew Number
2Dr Bohrgasse 7Dr Bohrgasse7
312 Mahlerstrasse Stiege 512 Mahlerstrasse Stiege5
4Zieglergasse 28Zieglergasse28
5Grunau 11Grunau11
6Schubertring 8Schubertring8
7Hegelgasse 13-22AHegelgasse13-22A
8
Sheet1
Cell Formulas
RangeFormula
B2:B7B2=TRIM(SUBSTITUTE(A2,IFERROR(MID(SUBSTITUTE(A2,LEFT(SUBSTITUTE(A2," ",REPT(" ",99)),99)+0,""),AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,"-"},SUBSTITUTE(A2,LEFT(SUBSTITUTE(A2," ",REPT(" ",99)),99)+0,"")),1),99),MID(A2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,"-"},A2),1),99)),""))
C2:C7C2=IFERROR(MID(SUBSTITUTE(A2,LEFT(SUBSTITUTE(A2," ",REPT(" ",99)),99)+0,""),AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,"-"},SUBSTITUTE(A2,LEFT(SUBSTITUTE(A2," ",REPT(" ",99)),99)+0,"")),1),99),MID(A2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,"-"},A2),1),99))
 
Upvote 0
Upvote 0
Ok, 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
Lists
Cell Formulas
RangeFormula
B2:C7B2=LET(F,FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))),CHOOSE({1,2},LEFT(A2,F),MID(A2,F+1,10)))
Dynamic array formulas.


Please don't forget to update your account details. ;)
 
Upvote 0
Rashie said:
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.
Click to expand...
Try this:
 

Attachments

  • Screen Shot 2021-11-25 at 8.45.46 AM.png
    Screen Shot 2021-11-25 at 8.45.46 AM.png
    116.7 KB · Views: 9
Upvote 0
BigBeachBananas said:
Try this:
Click to expand...
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
 
Upvote 0
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
B8,B2:C7B2=LET(F,FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))), IF(ISERROR(MID(A2,F+1,1)*1),A2,CHOOSE({1,2},LEFT(A2,F),MID(A2,F+1,10))))
Dynamic array formulas.
 
Upvote 0
Solution
Fluff said:
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
B8,B2:C7B2=LET(F,FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))), IF(ISERROR(MID(A2,F+1,1)*1),A2,CHOOSE({1,2},LEFT(A2,F),MID(A2,F+1,10))))
Dynamic array formulas.
Click to expand...
This looks great but I can't get it to work, it just gives me #NAME? error.... Any ideas?
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Remove all periods from the end of a text string within a cell
Replies
8
Views
878
Fluff
Fluff
F
  • Solved
Remove numbers from end of string
Replies
8
Views
659
FryGirl
F
D
  • Question
Completely cleaning account numbers and storing with a specific format
Replies
2
Views
609
diversification
D
S
  • Solved
IF function excluding 0 and 9 in text string
Replies
4
Views
149
dreid1011
dreid1011
J
  • Solved
Excel VBA: how to format all as text in "text to columns" function
Replies
3
Views
517
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,965
Messages
6,122,500
Members
449,090
Latest member
RandomExceller01

Share this page

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