Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Parsing
- Thread starter Mattine
- Start date
SkipVought
Programmer
Hi,
How is your data delimited? Please post several examples.
If there is NO delimiter, you may have a problem determining multi word cities UNLESS you ALWAYS have a 2-character state and 5 character ZIP.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
Skip,
How is your data delimited? Please post several examples.
If there is NO delimiter, you may have a problem determining multi word cities UNLESS you ALWAYS have a 2-character state and 5 character ZIP.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
Skip,
You mention that the city name is a variable length, can we assume that the state and zip are fixed lengths (2 letters for state, not sure how many letters you have in a zip code - being from the UK an' all...)?
If there are 2 letters for state, a space, then say 6 digits for zip code, then try the following (assuming your data is in column A):
=right(trim(a1),6) [for the zip]
=MID(TRIM(A1),LEN(TRIM(A1))-8,2) [state]
=left(trim(a1),len(trim(a1))-10) [for the city]
Note that the trim is used to get rid of any leading or trailing spaces that may exist in your data, but which aren't obvious.
If there are 2 letters for state, a space, then say 6 digits for zip code, then try the following (assuming your data is in column A):
=right(trim(a1),6) [for the zip]
=MID(TRIM(A1),LEN(TRIM(A1))-8,2) [state]
=left(trim(a1),len(trim(a1))-10) [for the city]
Note that the trim is used to get rid of any leading or trailing spaces that may exist in your data, but which aren't obvious.
- Status