Formula to Extract text in Postal Address

[RetroMe]

I have a cell that contains a full address e.g. Flat 10, 1 London Road, Bromley, Kent, BR1 1AA. I would like to be able to extract each of the parts of the address that are seperated by a comma.

I have managed to get the first line with:

=LEFT(A1,FIND(", ",A1-1)

And the second with:

=SUBSTITUTE(MID(SUBSTITUTE("," & A1&REPT(" ",6),",",REPT(",",255)),2*255,255),",","")

But I don't know how to get the third, fourth and fifth.

Any help would be greatly appreciated.

Many thanks.

 

[Dryver14]

Have you tried text to columns with this

 

[steve the fish]

This works going across:

=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",99)),(COLUMNS($A$1:A1)-1)*99+1,99))

 

[steve the fish]

As addresses can get quite long this is more bulletproof:

=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),(COLUMNS($A$1:A1)-1)*LEN($A1)+1,LEN($A1)))

 

[RetroMe]

Thanks Steve. That works for the first part of the address. What would I need to add to make it work for the 2,3,4,5 lines etc? Thanks.

 

[steve the fish]

You just drag it across. That will produce part 2. Then 3 etc.

 

[RetroMe]

You just drag it across. That will produce part 2. Then 3 etc.

Argh! I got it. I was dragging down. Works perfectly. Thank you so much.

 

[steve the fish]

If you want to drag down change columns in the formula to rows