Split Address In City & State Wise

**Mohanmoni** · 01-31-2015, 05:44 PM

Hi,

I am looking for formula that splits City and State from Address (USA) like check below address ends with State and city. From this can we able to split using "comma (,)".

Address:
------------------------------------------------------------
2210 San Ramon Valley Boulevard, San Ramon ,CA
2201 South Union Avenue, Chicago, IL
1 Old Country Road, Carle Place, NY
2828 West Parker Road b101, Plano, TX

In above address Last 2 Characters are State and before state is Cities. Now can we split Only last 2 comma(,) data.

Regards,
Mohan

**AlKey** · 01-31-2015, 05:54 PM

Try this in B1 and copy down

=REPLACE(A1,1,FIND(",",A1)+1,"")

Row\Col	A	B
1	2210 San Ramon Valley Boulevard, San Ramon ,CA	San Ramon ,CA
2	2201 South Union Avenue, Chicago, IL	Chicago, IL
3	1 Old Country Road, Carle Place, NY	Carle Place, NY
4	2828 West Parker Road b101, Plano, TX	Plano, TX

**Mohanmoni** · 01-31-2015, 07:45 PM

Same way if we have more then 2 comma..

Address:
------------------------------------------------------------
2210, San Ramon Valley, Boulevard, San Ramon ,CA
2201 South Union, Avenue, Chicago, IL
1 Old, Country Road, Carle Place, NY
2828, West Parker Road, b101, Plano, TX

**AlKey** · 01-31-2015, 08:14 PM

Like this?

=TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",25)),25*2))

Row\Col	A	B
1	2210, San Ramon Valley, Boulevard, San Ramon ,CA	San Ramon CA
2	2201 South Union, Avenue, Chicago, IL	Chicago IL
3	1 Old, Country Road, Carle Place, NY	Carle Place NY
4	2828, West Parker Road, b101, Plano, TX	Plano TX

Or if you want state separated by comma use this

=REPLACE(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",25)),25*2)),LEN(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",25)),25*2)))-2,1,", ")