If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sort pages? | David | General Discussion | 15 | May 13th, 2005 11:33 PM |
transpose | john | Using Forms | 1 | November 24th, 2004 06:16 PM |
How do I get 3 series in sync with the x-axis? | zizbird | Charts and Charting | 10 | October 25th, 2004 01:23 PM |
Excel Worksheets | Richard | General Discussion | 2 | July 8th, 2004 04:34 AM |
Mial merge data base problems | Rachael | Mailmerge | 16 | May 21st, 2004 06:22 PM |