Address splitting

Good Day All,

I am having a battle splitting and address cell, that contains 4 parts as below

92 Wayward Walk, Amberlain, Greytown, 1234

and using the LEFT, MID & RIGHT commands, i can only seperate the first, second & third and the last components

please help, help, help

Gordon Jamieson

If Your address is in A1 and the splits are in A3,A4,A5 and A6 then

92 Wayward Walk, Amberlain, Greytown, 1234

=LEFT(A1,FIND(",",A1))
=TRIM(MID(A$1,(FIND(A3,A$1)+LEN(A3)),FIND(",",A$1,(FIND(A3,A$1)+LEN(A3)))-(FIND(A3,A$1)+LEN(A3))+1))
=TRIM(MID(A$1,(FIND(A4,A$1)+LEN(A4)),FIND(",",A$1,(FIND(A4,A$1)+LEN(A4)))-(FIND(A4,A$1)+LEN(A4))+1))
=RIGHT(A$1,LEN(A$1)-FIND(A5,A$1)-LEN(A5))

This works, but is horrible. The probem is the find function gives you the start position of the string when what you want is the end position.

Can anyone fiind a better way?

Mark.

Thanks Guys,

I cannot believe i did'nt even think of the first option , but WOW on the second.

Thanks for your efforts and assistance, it was greatly appreciated, this is going to save a co-worker of mine about 6 hours of work

Regards Gordon