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:
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
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
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
:
Re: Formula to Separate 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
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
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 <
[email protected]>
wrote:
t
Submitted via EggHeadCafe - Software Developer Portal of Choice
How to detect and Automatically install MSXML
http://www.eggheadcafe.com/tutorial...1-04094072c374/how-to-detect-and-automat.aspx