Split String from Right

[Blessy Clara]

Hi

There are about 10,000 rows of address

Sample

243 River Street Ballina

Bundaberg

Dunsborough

Suite 5 Kawana Prof Cnt 134A Pt Cartwright Drive Buddina

Suite 3/170 Gooding Drive Merrimac

1 Bangalow Road Ballina

921 Station St, Box Hill North

112 Drummond Street North, Ballarat

173 Hampton Street, Bridge town <tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

Requirement - Want to split the string from Right

with the First space for those without coma

243 River Street

Ballina

<tbody>
</tbody>

and for those records with coma it should be the first occurrence of coma from right
112 Drummond Street North Ballarat

173 Hampton Street Bridge town

<tbody>
</tbody>

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