Split String from Right

[Blessy Clara]

Hi

There are about 10,000 rows of address

Sample
[TABLE="width: 423"]
<colgroup><col></colgroup><tbody>[TR]
[TD]243 River Street Ballina[/TD]
[/TR]
[TR]
[TD]Bundaberg[/TD]
[/TR]
[TR]
[TD]Dunsborough[/TD]
[/TR]
[TR]
[TD]Suite 5 Kawana Prof Cnt 134A Pt Cartwright Drive Buddina[/TD]
[/TR]
[TR]
[TD]Suite 3/170 Gooding Drive Merrimac[/TD]
[/TR]
[TR]
[TD]1 Bangalow Road Ballina[/TD]
[/TR]
[TR]
[TD]921 Station St, Box Hill North[/TD]
[/TR]
[TR]
[TD]112 Drummond Street North, Ballarat[/TD]
[/TR]
[TR]
[TD] [TABLE="width: 347"]
<tbody>[TR]
[TD="class: xl68, width: 347"]173 Hampton Street, Bridge town

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Requirement - Want to split the string from Right

with the First space for those without coma
[TABLE="width: 487"]
<tbody>[TR]
[TD="class: xl68, width: 423"]243 River Street [/TD]
[TD="width: 64"]Ballina[/TD]
[/TR]
</tbody>[/TABLE]

and for those records with coma it should be the first occurrence of coma from right
112 Drummond Street North Ballarat
[TABLE="width: 347"]
<tbody>[TR]
[TD="class: xl68, width: 347"]173 Hampton Street Bridge town[/TD]
[/TR]
</tbody>[/TABLE]
Those highlighted in Red to be split to next columns

Using Text to column or Formula may not be helpful as the count/length of string varies

Thank you

 

[mikerickson]

Try

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,","," ")," ",REPT(" ",255)),255))

 

[Blessy Clara]

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 423"]
<tbody>[TR]
[TD="class: xl68, width: 423"]243 River Street[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 423"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Ballina[/TD]
[/TR]
[TR]
[TD][TABLE="width: 423"]
<tbody>[TR]
[TD="class: xl68, width: 423"]112 Drummond Street North[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 423"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 423"]
<tbody>[TR]
[TD="class: xl68, width: 423"]Ballarat[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 423"]
<tbody>[TR]
[TD="class: xl68, width: 423"]Suite 3/170 Gooding Drive [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Merrimac[/TD]
[/TR]
</tbody>[/TABLE]

Hi Thank you, but the purpose is to split suburb from address as above,

the formula fetches the term though.

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 423"]
<tbody>[TR]
[TD="class: xl68, width: 423"]243 River Street Ballina[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Ballina[/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

Then use a formula like this
=SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))

to replace the last space with ~,
Then use TextToColumns to split with a ~ delimiter.
Note that the last screen of the TextToColumns dialog has a Destination that can be used to write the split data over the old location.

 

[Blessy Clara]

Wow Cool, yeah - thank you very very much it is very helpful.

 

[Blessy Clara]

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 337"]
<tbody>[TR]
[TD="class: xl66, width: 337"]1 Intrepid Dr, Victoria [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1 Intrepid Dr[/TD]
[TD]Victoria [/TD]
[/TR]
[TR]
[TD]1 Leghorn St Rockingham[/TD]
[TD]1 Leghorn St[/TD]
[TD]Rockingham[/TD]
[/TR]
[TR]
[TD]173 Oxley Avenue, Woody Point
[/TD]
[TD]173 Oxley Avenue [/TD]
[TD]Woody Point[/TD]
[/TR]
[TR]
[TD]Post Formula[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 337"]
<tbody>[TR]
[TD="class: xl66, width: 337"]1 Intrepid Dr, Victoria Point[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 337"]
<tbody>[TR]
[TD="class: xl66, width: 337"]1 Intrepid Dr, Victoria[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Point[/TD]
[/TR]
[TR]
[TD]1 Leghorn St Rockingham[/TD]
[TD]1 Leghorn St [/TD]
[TD]Rockingham[/TD]
[/TR]
[TR]
[TD]206 Stirling Hwy, Brighton


[/TD]
[TD]206 Stirling Hwy, [/TD]
[TD]Brighton[/TD]
[/TR]
[TR]
[TD]173 Oxley Avenue, Woody Point[/TD]
[TD]173 Oxley Avenue, Woody [/TD]
[TD]Point[/TD]
[/TR]
</tbody>[/TABLE]

Sorry - but i am left with two concerns

a coma at the end for those records that had coma as criteria - and when there are two words after coma only one word is split

 

[mikerickson]

So the rule is
If no comma, split at last space
otherwise, split at last comma.

correct?

 

[Blessy Clara]

Yes Exact -

 

[mikerickson]

I'm at work and can't put it together right now, but I'd have one column with

=IF(LEN(A1)=LEN(SUBSTITUTE(A1,",",""), replace last space with ", ", A1)

then another helper column with a "replace last , with ~" formula, and then use TextToColumns

Those "replace last.." formulas would look like the formula above.

 

[Blessy Clara]

Thanks alot, for your time. yeah have been using it as stated