Select Data

S

Stefan

Is there a way to use the LEFT() function to take all the characters until a
space is encountered?

I am trying to normalize data I imported from another program and the City
State and zip are all in one field there are no comma's.

I used Right() function to get the 5 digit zip code.

example
Honolulu Hi 96740
 
D

Duane Hookom

You can find the space with an expression like:
Instr([CityStateAndZip], " ")
Use this with Left() to get everything left of the space.
 
J

John Spencer (MVP)

Watch out for city names like New Orleans, New York, St. Claire, Sioux City,
North Platte, etc.

IF (very important word) your fields always contain City Name, space,
2-Character State Code, space, and a 5 digit zip. Such as
New York NY 12345

City:MID([YourField],1,Len([YourField])-9)

State: MID([YourField],Len([YourField])-8,2)

Zip: You've already figured out.

Duane said:
You can find the space with an expression like:
Instr([CityStateAndZip], " ")
Use this with Left() to get everything left of the space.

--
Duane Hookom
MS Access MVP

Stefan said:
Is there a way to use the LEFT() function to take all the characters until
a
space is encountered?

I am trying to normalize data I imported from another program and the City
State and zip are all in one field there are no comma's.

I used Right() function to get the 5 digit zip code.

example
Honolulu Hi 96740
Click to expand...
Click to expand...
 
Top