Formula to Separate City, State, Zip
 

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

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(",",SUBS TITUTE(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" wrote in message
...
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

Formula to Separate City, State, Zip
 

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
wrote:
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

Formula to Separate City, State, Zip
 

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:

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

Formula to Separate City, State, Zip
 

in article , Havenstar at
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

Formula to Separate City, State, Zip
 

I would try copying the column to sheet2 then run it through DataText to
ColumnsDelimited by comma.


Gord Dibben MS Excel MVP

On Wed, 15 Oct 2008 12:32:04 -0700, Havenstar
wrote:

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

Formula to Separate City, State, Zip
 

Thanks for this! It helped me tremendously!



Havensta wrote:

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

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

Previous Posts In This Thread:

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

Formula to Separate City, State, Zip
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

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

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(",",SUBS TITUTE(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" wrote in message
...

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:

Formula to Separate City, State, Zip
in article , Havenstar at
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 DataText
I would try copying the column to sheet2 then run it through DataText to
ColumnsDelimited 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(",",Sheet 1!A1))+1,LEN(Sheet1!
A1)-LEN(Sheet2!A1)-LEN(Sheet2!C1)-2)
C1 =3DMID(Sheet1!A1,FIND(CHAR(39),SUBSTITUTE(Sheet1!A 1,",",CHAR(39),2))
+1,255)



On Oct 16, 12:32=A0am, Havenstar
wrote:
t


Submitted via EggHeadCafe - Software Developer Portal of Choice
How to detect and Automatically install MSXML
http://www.eggheadcafe.com/tutorials...d-automat.aspx