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

 

[Peter_SSs]

Does this direct formula do what you want?

Excel Workbook
	A	B
1	243 River Street Ballina	Ballina
2	Suite 5 Kawana Prof Cnt 134A Pt Cartwright Drive Buddina	Buddina
3	Suite 3/170 Gooding Drive Merrimac	Merrimac
4	1 Bangalow Road Ballina	Ballina
5	921 Station St, Box Hill North	Box Hill North
6	112 Drummond Street North, Ballarat	Ballarat
7	173 Hampton Street, Bridge town	Bridge town
Split Text

 

[Blessy Clara]

Peter_SSs Oh yeah, thank you very much, this is very helpful too at one go.

 

[Peter_SSs]

Peter_SSs Oh yeah, thank you very much, this is very helpful too at one go.

You are welcome