Get all characters on far right sight up to a space

jebenexcel

Board Regular
Joined
Mar 16, 2018
Messages
59
Hi,

How would one go about getting the last set/word of characters on the right side of a string, delimited by the first space from the right without inserting any new cells?
I need to separate some addresses into street names and house numbers; the numbers are 1-999 but sometimes they are letters as well, so I can't just remove all non-number characters to do that.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
There may be a shorter way

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(((LEN(A1)-LEN(SUBSTITUTE(A1," ","")))+1)*(LEN(A1))-LEN(A1))+1,LEN(A1)))
 

jebenexcel

Board Regular
Joined
Mar 16, 2018
Messages
59
I used this, found it somewhere with google. Inserts 100 spaces between words, takes the last 100 hundred characters, changes spaces to "".

=IF(A2="";"";TRIM(RIGHT(SUBSTITUTE(K2;" ";REPT(" ";100));100)))

Thanks for the help anyway!!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,020
Messages
5,526,296
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top