How to Split the addresses in cells

[niladri20052006]

Hi All,

I have the address in A2 like below...

240 Commerce,Irvine, CA, 92602-1318

I want to split all my addresses not by text to Columns but by formula.

Is there any formula so that street, City, State and postal code will be in each separate cell.

I want to use formula...

Thanks in advance!

 

[laughingdevil]

I think you want somthing like this

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

in each cell you want the different bits in, so in your example the above will give you

240 Commerce

 

[niladri20052006]

Hi

Thanks for your quick reply

But I want like below..

240 Commerce Irvine CA 92602-1318



All the above four will be in separate cell.

Is it possible by using formula?

Waiting for your reply..

 

[Marcelo Branco]

Hi,

Try this formula in B2

=TRIM(MID(SUBSTITUTE(","&$A$2,",",REPT(" ",200)),200+200*(COLUMNS($B$2:B2)-1),200))

copy across to C2, D2, E2

HTH

M.

 

[niladri20052006]

Hi Marcelo,

Thanks a lot....

 

[Marcelo Branco]

Hi Marcelo,

Thanks a lot....

You are welcome

By the way, simplify the formula to

=TRIM(MID(SUBSTITUTE(","&$A$2,",",REPT(" ",200)),200*COLUMNS($B$2:B2),200))

M.