Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Feb 2008
    Posts
    28

    Unanswered: Split Field in Table

    Hello,

    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?

    Thanks,

    Thaas

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2008
    Posts
    28
    Well yes actually. I was reading that, but I am not sure I understand how to do what you have suggested.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There are several suggestions in there.

    Could you tell us the format of the data in your field? For example, are there delimiters (oh please let there be delimiters )?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2008
    Posts
    28
    I have one field called Client. In this field there may be any number of the following pieces of data:

    First Name, Spouse Name, Last Name Street, City, State, Zip

    Example: Bob & Sue Smith 405 Thatstreet Water Grove, CA 95818

    There is a comma between City and State. Each piece of data is separated by a " ". The cities will be more than likely one of two different cities. It is Text.

    I tried creating a query with your suggestion:

    WHERE (LEN(Client) - 4) <> LEN(REPLACE(Client, "/", ""))

    I received the message Datatype error in criteria expression. Each record changed to #Name?.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2008
    Posts
    28
    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.

  8. #8
    Join Date
    Feb 2008
    Posts
    28
    Actually,

    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.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - you are right - you probably could parse this from right to left.

    Code:
    UPDATE myTable
    SET zip = RIGHT(myField, 5)
    FROM myTable
    Code:
    UPDATE myTable
    SET myField = MID(myField, 1, LEN(myfield) - 5))
    FROM myTable
    Is state always CA? If so, knock more off the LEN() value above.

    Code:
    UPDATE myTable
    SET city = SWITCH(myField LIKE "*city a*", "city a", 1 = 1, "city b")
    FROM myTable
    Code:
    UPDATE myTable
     SET myField = REPLACE(REPLACE(myField, " city a, ", ""), " city b, ", "")
     FROM myTable
    The next bit is trickier of course....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by thaas
    there are only 9 choices for that digit at the beginning of the street address.
    This is a pain in Access. In SQL Server you have PATINDEX which would be perfect (allows wildcards). Does Excel have a function like instr but which allows wildcards?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2008
    Posts
    28
    OK. Here is my SQL code:

    UPDATE Clubz
    SET zip = Right (client, 5)
    FROM Clubz;

    and here is my error message:

    Syntax error (missing operator) in query expression 'Right (Client, 5) FROM Clubz

    Do you have any suggestions? I am guessing I build a query in SQL using access . It is an update query i would imagine.

  12. #12
    Join Date
    Feb 2008
    Posts
    28
    I don't know if excel has a wildcard function...

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't write much JET SQL - I got myself confuzzlated with T-SQL

    Code:
     UPDATE Clubz
    SET zip = Right (client, 5)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2008
    Posts
    28
    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:

    10001 that street sacramento, ca

    in a new field. Right?

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Several problems. Split is VBA - you can't use it in queries. Unless you are up to writing all this in VBA forget Split().

    Also, split would produce the following for your sample data (10001 that street sacramento, ca):

    First element of array produced by split: ""
    Second element of array produced by split: "000"
    Third element of array produced by split: " that street sacramento, ca"
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •