Select Data

[Guest]

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

 

[Duane Hookom]

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

 

[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.

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

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