Formula to Separate City, State, Zip

Havenstar

unread,

Oct 15, 2008, 3:32:04 PM10/15/08

to

Hi,

I need to find a formula to separate the City, State, Zip onto a different
spreadsheet

Work Sheet 1
Column A
City, State, Zip

Work Sheet 2
Column A | Column B | Column C
City | State | Zip

Any help would be appreciated.

Thank you!
Havenstar

muddan madhu

unread,

Oct 15, 2008, 4:06:00 PM10/15/08

to

try this

in Work sheet 2 put this formula

A1 =LEFT(Sheet1!A1,FIND(",",Sheet1!A1)-1)
B1 =MID(Sheet1!A1,FIND(",",Sheet1!A1,FIND(",",Sheet1!A1))+1,LEN(Sheet1!
A1)-LEN(Sheet2!A1)-LEN(Sheet2!C1)-2)
C1 =MID(Sheet1!A1,FIND(CHAR(39),SUBSTITUTE(Sheet1!A1,",",CHAR(39),2))
+1,255)

On Oct 16, 12:32 am, Havenstar <Havens...@discussions.microsoft.com>
wrote:

Bernard Liengme

unread,

Oct 15, 2008, 4:01:51 PM10/15/08

to

Provides we always have comma followed by one space:

City: =LEFT(Sheet1!A1,FIND(",",Sheet1!A1)-1)

State:
=MID(Sheet1!A1,FIND(",",Sheet1!A1)+2,FIND(",",SUBSTITUTE(Sheet1!A1,",","",1))-FIND(",",Sheet1!A1)-1)

Zip:=MID(Sheet1!A1,FIND(",",SUBSTITUTE(Sheet1!A1,",","",1))+3,255)

Replace Sheet1 by name of worksheet. If name has one or more spaces then use
single quotes
City: =LEFT('My data sheet!A1,FIND(",",'My data sheet'!A1)-1)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Havenstar" <Have...@discussions.microsoft.com> wrote in message
news:C8A6E376-9923-490D...@microsoft.com...

tedmi

unread,

Oct 15, 2008, 4:15:02 PM10/15/08

to

Assuming that the 3 parts of the address are separated by comma blank, and
assuming your data starts in Row 2, put this in WS1:
in B2 =Left(A2, Find(", ", A2)-1)
in C2 =Mid(A2, Find(", ", A2)+2, 999)
in D2 =Left(C2 Find(", ", C2)-1)
in E2 =Mid(C2, Find(", ", C2)+2, 999)
Copy cols B-E down as far as there is data in col A

in WS2:
in A2 =WorkSheet1!B2
in B2 =WorkSheet1!D2
in C2 =WorkSheet1!E2
and copy down.
--
TedMi

Jim May

unread,

Oct 15, 2008, 4:15:49 PM10/15/08

to

in article C8A6E376-9923-490D...@microsoft.com, Havenstar at
Have...@discussions.microsoft.com wrote on 10/15/08 3:32 PM:

> City, State, Zip
For city =LEFT(A1,FIND(",",A1)-1)

For state =MID(A1,FIND(",",A1)+2,2)

For zip =RIGHT(A1,5)

Hope that helps,

Jim

Gord Dibben

unread,

Oct 15, 2008, 7:53:10 PM10/15/08

to

I would try copying the column to sheet2 then run it through Data>Text to
Columns>Delimited by comma.

Gord Dibben MS Excel MVP

kathyjuliussen

unread,

Jan 21, 2010, 1:24:50 PM1/21/10

to

Thanks for this! It helped me tremendously!

Havensta wrote:

Formula to Separate City, State, Zip
15-Oct-08

Hi,

Thank you!
Havenstar

Previous Posts In This Thread:

On Wednesday, October 15, 2008 3:32 PM
Havensta wrote:

Formula to Separate City, State, Zip
Hi,

Thank you!
Havenstar

On Wednesday, October 15, 2008 4:01 PM
Bernard Liengme wrote:

Re: Formula to Separate City, State, Zip

Provides we always have comma followed by one space:

City: =LEFT(Sheet1!A1,FIND(",",Sheet1!A1)-1)

State:
=MID(Sheet1!A1,FIND(",",Sheet1!A1)+2,FIND(",",SUBSTITUTE(Sheet1!A1,",","",1))-FIND(",",Sheet1!A1)-1)

Zip:=MID(Sheet1!A1,FIND(",",SUBSTITUTE(Sheet1!A1,",","",1))+3,255)

Replace Sheet1 by name of worksheet. If name has one or more spaces then use
single quotes
City: =LEFT('My data sheet!A1,FIND(",",'My data sheet'!A1)-1)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Havenstar" <Have...@discussions.microsoft.com> wrote in message
news:C8A6E376-9923-490D...@microsoft.com...

On Wednesday, October 15, 2008 4:15 PM
tedm wrote:

Assuming that the 3 parts of the address are separated by comma blank, and
Assuming that the 3 parts of the address are separated by comma blank, and
assuming your data starts in Row 2, put this in WS1:
in B2 =Left(A2, Find(", ", A2)-1)
in C2 =Mid(A2, Find(", ", A2)+2, 999)
in D2 =Left(C2 Find(", ", C2)-1)
in E2 =Mid(C2, Find(", ", C2)+2, 999)
Copy cols B-E down as far as there is data in col A

in WS2:
in A2 =WorkSheet1!B2
in B2 =WorkSheet1!D2
in C2 =WorkSheet1!E2
and copy down.
--
TedMi

"Havenstar" wrote:

On Wednesday, October 15, 2008 4:15 PM
Jim May wrote:

Re: Formula to Separate City, State, Zip

in article C8A6E376-9923-490D...@microsoft.com, Havenstar at
Have...@discussions.microsoft.com wrote on 10/15/08 3:32 PM:

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

For state =MID(A1,FIND(",",A1)+2,2)

For zip =RIGHT(A1,5)

Hope that helps,

Jim

On Wednesday, October 15, 2008 7:53 PM
Gord Dibben wrote:

I would try copying the column to sheet2 then run it through Data>Text
I would try copying the column to sheet2 then run it through Data>Text to
Columns>Delimited by comma.

Gord Dibben MS Excel MVP

On Saturday, October 18, 2008 12:25 AM
muddan madhu wrote:

try thisin Work sheet 2 put this formulaA1 =3DLEFT(Sheet1!A1,FIND(",",Sheet1!
try this

in Work sheet 2 put this formula

A1 =3DLEFT(Sheet1!A1,FIND(",",Sheet1!A1)-1)
B1 =3DMID(Sheet1!A1,FIND(",",Sheet1!A1,FIND(",",Sheet1!A1))+1,LEN(Sheet1!
A1)-LEN(Sheet2!A1)-LEN(Sheet2!C1)-2)
C1 =3DMID(Sheet1!A1,FIND(CHAR(39),SUBSTITUTE(Sheet1!A1,",",CHAR(39),2))
+1,255)

On Oct 16, 12:32=A0am, Havenstar <Havens...@discussions.microsoft.com>
wrote:
t

Submitted via EggHeadCafe - Software Developer Portal of Choice
How to detect and Automatically install MSXML
http://www.eggheadcafe.com/tutorials/aspnet/9db92980-4f3a-4243-b411-04094072c374/how-to-detect-and-automat.aspx