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

 

[mikerickson]

Try

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

 

[Blessy Clara]

243 River Street <tbody> </tbody>	Ballina
112 Drummond Street North <tbody> </tbody>	Ballarat <tbody> </tbody>
Suite 3/170 Gooding Drive <tbody> </tbody>	Merrimac

<tbody>
</tbody>

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

the formula fetches the term though.

243 River Street Ballina <tbody> </tbody>

Ballina

<tbody>
</tbody>

 

[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]

1 Intrepid Dr, Victoria <tbody> </tbody>	1 Intrepid Dr	Victoria
1 Leghorn St Rockingham	1 Leghorn St	Rockingham
173 Oxley Avenue, Woody Point	173 Oxley Avenue	Woody Point
Post Formula
1 Intrepid Dr, Victoria Point <tbody> </tbody>	1 Intrepid Dr, Victoria <tbody> </tbody>	Point
1 Leghorn St Rockingham	1 Leghorn St	Rockingham
206 Stirling Hwy, Brighton	206 Stirling Hwy,	Brighton
173 Oxley Avenue, Woody Point	173 Oxley Avenue, Woody	Point

<tbody>
</tbody>

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