Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Nov 2007
    Posts
    16

    Unanswered: Splitting a column

    This is probably Databse Programming 101, but I am totally green. All I want to do is the following:

    I have a table for a mailing list: It has a field for name, address, city state and zip. The problem is that the name field is set up to where it reads as follows:

    Last name --- Space --- First name --- (if there is more than one first name) --- space --- other first name / (spouse)

    I need to get the last name put into a separate field so I can do the mailer... otherwise the mailing labels will read:

    " Johnson Bill and Mary" - Looks dorky!

    Can someone walk me through how to do this in either Excel (which is what I have it in now) or in Access (which I know a little about, but not much) - I know how to create an Access DB and put the values in the DB, but that's about as far as I have gotten.

    Thanks in advance,

    Steve
    Last edited by D-Dub; 11-21-07 at 21:32.
    I'm Old, I'm Grumpy, and I have no clue what I'm doing

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    As you are in Excel then I suggest extracting the first word from Cell A2 with this

    =LEFT(TRIM(A2),FIND(" ",TRIM(A2)))

    and the second word with this

    =MID(TRIM(A2),FIND(" ",TRIM(A2))+1,FIND(" ",TRIM(A2),FIND(" ",TRIM(A2))+1)-FIND(" ",TRIM(A2)))

    I've used TRIM() to eliminate leading spaces (it also eliminates multiple spaces between words).

    Any good ?

    MTB
    ps You can also concatenate them together to give you 'Bill Johnson' like this

    =MID(TRIM(A2),FIND(" ",TRIM(A2))+1,FIND(" ",TRIM(A2),FIND(" ",TRIM(A2))+1)-FIND(" ",TRIM(A2))) & " " & LEFT(TRIM(A2),FIND(" ",TRIM(A2)))
    Last edited by MikeTheBike; 11-22-07 at 08:34.

  3. #3
    Join Date
    Nov 2007
    Posts
    16
    Thanks for the help! Much appreciated.

    While that does solve part of the problem, it does not give me the entire solution:

    The first part of the functin separates the first value of the field, and the second function the second word. The thing I want to do is extract the first word out of the cell, and then the rest, e.g the "balance" of the characters into a second cell. This is definately a step in the right direction... I just need to figure out how to do the rest.

    I guess it would soirk if I could not only separate the first value and put it in a different cell but, if I could extract it and eliminate it from the first cell, that would work too.
    I'm Old, I'm Grumpy, and I have no clue what I'm doing

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    In Access, the split function is your friend which returns an array of terms based in the split character, wonder if its the same in Excel

    eg
    strwords = split(mydata," ")

    if you place it in a function you can then reprocess / reassemble htose terms as required

    I think your problem is going to be on how you define what you re-assemble the name as
    for example
    smith john and mary ..... easy John and mary smith
    if however someone had a double barreled name Smith Jones
    smith jones john and mary.. how do you know it should be John and mary smith jones

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by D-Dub
    Thanks for the help! Much appreciated.

    While that does solve part of the problem, it does not give me the entire solution:

    The first part of the functin separates the first value of the field, and the second function the second word. The thing I want to do is extract the first word out of the cell, and then the rest, e.g the "balance" of the characters into a second cell. This is definately a step in the right direction... I just need to figure out how to do the rest.

    I guess it would soirk if I could not only separate the first value and put it in a different cell but, if I could extract it and eliminate it from the first cell, that would work too.
    in that case you could look for the first instance of a space and chop it off there

    .assuming that you KNOW all records have at least one space.....
    .. it could be done in a query
    select left(mynames,instr(mynames," ") as surname, mid(mynames,instr(mynames," ")+1) as ForeNames from my table

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    If you still want to stick with Excel then, this returns the first word

    =LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1)

    and this the remainder (lightly easier the the last one).

    =MID(TRIM(A2),FIND(" ",TRIM(A2))+1,LEN(TRIM(A2)))

    As you will see from healdem's post this is virtually the same as the code for the query, so the choice is yours!

    MTB

  7. #7
    Join Date
    Nov 2007
    Posts
    16
    OK - First, thanks for putting up with me --- this must be pretty boring for you two:

    I got the Excel function --- easy as copy / paste and viola! I've been trying to get it in Access too --- I dont just want the solution --- I'm also using this time as a learning experience.

    I tried to do the query in Access but failed. I imported the Excel file into access and did the following:

    Eliiminated all the columns except the name field
    Named the table "Tablenames"
    Named the remaining field "MyNames"
    Added two additional fields: "Firstnames" and "Lastname"
    Started a query, selected the table, and chose "MyNames"as the field.

    In the criteria box I typed the following:

    select left(MyNames,instr(MyNames," ") as Lastnames, mid(MyNames,instr(MyNames," ")+1) as FirstNames from Tablenames

    It returns an error "The syntax of the subquery in this expression is incorrect"

    What am I missing?
    I'm Old, I'm Grumpy, and I have no clue what I'm doing

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you don't need to create the columns LastNames and FirstNames in the query

    the select should be something like

    SELECT Left(MyNames,InStr(MyNames," ")) AS Surname, Mid([MyNames],InStr([MyNames]," ")+1) AS ForeNames
    FROM TableNames;

    admire the approach by the way
    I dont just want the solution --- I'm also using this time as a learning experience.
    its not a bother when someone genuinely wants to learn

  9. #9
    Join Date
    Nov 2007
    Posts
    16
    Quote Originally Posted by healdem
    you don't need to create the columns LastNames and FirstNames in the query
    OK - I pulled them out

    Quote Originally Posted by healdem
    SELECT Left(MyNames,InStr(MyNames," ")) AS Surname, Mid([MyNames],InStr([MyNames]," ")+1) AS ForeNames
    FROM TableNames;
    I copied it exactly and it returned the same response. It also says:
    "Check the subquery's syntax and enclose the subquery in parenthesis"

    Then I tried adding a set of parenthesis in different locations, like this:

    SELECT Left(MyNames,InStr(MyNames," ")) AS Surname, Mid(([MyNames],InStr([MyNames]," ")+1)) AS ForeNames
    FROM TableNames


    Plus every other variation I could think of... no luck. Not sure what I'm doing but I like trying to figure it out.
    Last edited by D-Dub; 11-22-07 at 10:51.
    I'm Old, I'm Grumpy, and I have no clue what I'm doing

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it possibly should be
    Code:
    SELECT Left([MyNames],InStr(MyNames," ")) AS Surname, Mid([MyNames],InStr([MyNames]," ")+1) AS ForeNames
    FROM TableNames;
    ...if that fails
    copy your db to something else
    delete all objects except the table and the query
    compact and repair the DB, zip it and post iut here I'll have a look it

    I did test the query in Access XP so it did work on the closest I could get to your data, but I'm happy to have another look at it.

  11. #11
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I pasted healman's query into query designer and it work ok, except, as I have used your example sting in the first post having a leading space, therefore it needed the trim function as indicated previously, ie

    SELECT Left(TRIM(MyNames),InStr(TRIM(MyNames)," ")) AS Surname, Mid(TRIM([MyNames]),InStr(TRIM([MyNames])," ")+1) AS ForeNames
    FROM TableNames;

    So I don't know what is wrong!?

    MTB

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...the clue may be in the words sub query....... are you using this as a simple query or a query within a query?

  13. #13
    Join Date
    Nov 2007
    Posts
    16
    I tried to do what you said but I am obviously missing something simple. I guess simple solutions for simple problems are complex for people like me

    Access would not allow me to save the query so all I have is the data. KNock yourself out!
    Attached Files Attached Files
    I'm Old, I'm Grumpy, and I have no clue what I'm doing

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    sure it works!

    the last three query suggestions work as-advertised out-of-the-box.

    see in attached
    q1 - straight copy/paste from post #11
    q1faster - using left$() mid$() and trim$() which should run +/- in half the time


    mmmmmmmm - were you opening your query in SQL-view and copy/pasting into there?

    izy
    Attached Files Attached Files
    currently using SS 2008R2

  15. #15
    Join Date
    Nov 2007
    Posts
    16
    I copied and pasted directly into the query, but I must be doing it in the wrong place. Here's a pic of where I am putting it and what the message says
    Attached Thumbnails Attached Thumbnails DBSH1.JPG  
    I'm Old, I'm Grumpy, and I have no clue what I'm doing

Posting Permissions

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