I have an excel spreadsheet that contains First Last Address City State Zip in one field. If I import into Access it is imported into one field. How can I separate all these items into separate fields without doing it one at a time?
That clause is not much use to you in any event - it was written with the other person's particular requirements in mind.
I am afraid you look pretty well stuffed - whoever formatted the data together like that has stitched you up. Are you able to go back to the source and get the data properly delimited? A space is just about the world's worst delimiter, especially with no characters enclosing the actual data.
I was thinking someone might say that I received the data in this format expressly against my wishes and around 6pm from an exasperated office assistant... i have a deadline in the morning so i suppose i'll just have to start by hand... Thanks though for taking a look at it.
Would you suppose there is any way to pull out of that field everything to the right of the first digit in that field, since there are only 9 choices for that digit at the beginning of the street address. I could then have a field that contained for example 10001 Thatstreet Sacramento, CA 95667. From there I could grab the last 5 digits of the field and put that into another field. And then I would be left with a city and state. There are only 2 choices for the city. The city and state are separated by a comma.
OK, so i have the zip code out into a field called zip. No i dont care about the CA really because its all in CA.
Can I use a digit as a delimitor? For example, with a Split() function could I seperate everything from the Client field that starts with the digit "1" and get all addresses that begin with 1? And so on through 9. That should give me something like: