Select Data

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


--
Thanks in advance
Stefan

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" wrote in message
...
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


--
Thanks in advance
Stefan

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

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" wrote in message
...
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


--
Thanks in advance
Stefan