Splitting addresses

[Liverlee]

Hello All

I've thousands of addresses in column A that need splitting because they are too long. Some addresses are 31 characters in length and others are up to 100 characters in length. There are no commas or semi-colons that split the addresses sadly. There are random amounts of spaces between each text string some may have the first space after 3 characters like a house number "103" and for some addresses, the first space might be after 13 characters i.e "apartment 103", it's all completely random.

So for example i may have two addresses
In A1 - '1600 Pennsylvania Avenue NW Washington DC 20500 USA' has 7 spaces in the 51-character text string
In A2 - '1 Buckingham palace London England' has 4 spaces in a 34-character text string

Is there a way to split a cell text string to the last space before 30 characters with a comma but not through a whole word? so in the above case there'd be a comma inserted after 'NW'

i've tried =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)&", "&RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)) and =LEFT(A1,29)&","&RIGHT(A1,LEN(A1)-29)

Any and all suggestions are welcome, racking my brain on a Saturday evening trying to come up with a solution.

 

[Fluff]

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’)

If you have 365 with the latest functions, how about

Fluff.xlsm
	A	B
1	1600 Pennsylvania Avenue NW Washington DC 20500 USA	1600 Pennsylvania Avenue NW, Washington DC 20500 USA
2	1 Buckingham palace London England	1 Buckingham palace London, England
Main

Cell Formulas
Range	Formula
B1:B2	B1	=LET(a,TEXTBEFORE(LEFT(A1,30)," ",-1),a& ","& SUBSTITUTE(A1,a,""))

 

[Liverlee]

2019

 

[Fluff]

Then why did you say you had sorted a previous question with "=textafter(a2,"*",-1)" as that doesn't exist in 2019?

 

[Liverlee]

I have access to office 365 via work. Not at home

 

[Peter_SSs]

I have access to office 365 via work.

So ...

• Perhaps update your account details to include both versions and/or explain in your post what version(s) you want the answer for.
• Did the suggestion in post #2 work for you?

Another possibility might be

22 10 16.xlsm
	A	B
1	1600 Pennsylvania Avenue NW Washington DC 20500 USA	1600 Pennsylvania Avenue NW, Washington DC 20500 USA
2	1 Buckingham palace London England	1 Buckingham palace London, England
3	23 Short address	23 Short address
Split Address

Cell Formulas
Range	Formula
B1:B3	B1	=IF(LEN(A1)>30,REPLACE(A1,LEN(TEXTBEFORE(LEFT(A1,30)," ",-1))+1,0,","),A1)

 

[Fluff]

Another option for 2019

Fluff.xlsm
	A	B
1	1600 Pennsylvania Avenue NW Washington DC 20500 USA	1600 Pennsylvania Avenue NW, Washington DC 20500 USA
2	1 Buckingham palace London England	1 Buckingham palace London, England
Main

Cell Formulas
Range	Formula
B1:B2	B1	=IF(LEN(A1)<31,A1,SUBSTITUTE(A1," ",", ",30-LEN(SUBSTITUTE(LEFT(A1,30)," ",""))))

 

[kvsrinivasamurthy]

Try this ARRAY formula.
A2 is data . In B2

=REPLACE(A2,MAX(IF(MID(A2,ROW(1:30),1)=" ",ROW(1:30),"")),1,", ")

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

 

[Liverlee]

thankyou for all responding, I spent today trying all your suggested formulas to split the 100k addresses from an old legacy system that's even more out of date than myself
and with your help it was done in a couple of clicks. bless you all.

 

[Fluff]

Glad we could help & thanks for the feedback.