Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking Unanswered: Seperating data into two fields

    Hi all and needing a bit of help. My friend has a program that has data, peoples names. The names however are First and Last name all in one field...sucks. How can I pull that data from that program and import into Excel 2000/2003 and put each into seperate fields? First name in one cell and last name in one cell. I believe the names are seperated with a comma. I know how to only do the Left worksheet function going for a known set of characters both Left and Right. But that won't work well for a list of about 500 names easily, not to mention they are all of varying lengths. Any tips on how to do this? Mind you I am not an Excel expert either. Now, I do use Access a lot (still no expert) so if I can pull it into Access and later spit it out to Excel, gives steps and I can follow.

    thanks and have a nice day,
    BUD

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    If you only need to do this once, paste the data into Excel, and then
    have a look at Data->TextToColumns.


    If you'll need to do it routinely, write an Access query:

    SELECT Left([Name],InStr([name],",")-1) AS FirstName,
    Right([name],InStr([name],",")+1) AS LastName
    FROM Table1;

    and export the results to Excel.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Or how about the good ol' Split function?

    Dim NameArray() as string, Dim tmpstring as string

    NameArray=Split("Jones, Harold T.",",")

    ' NameArray(0) contains "Jones"
    ' Name Array(1) contains "Harold T."

    You can iterate on the array using the For Each loop

    For Each tmpstring IN NameArray
    ...
    Next
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Red face

    Quote Originally Posted by RedNeckGeek
    If you only need to do this once, paste the data into Excel, and then
    have a look at Data->TextToColumns.


    If you'll need to do it routinely, write an Access query:

    SELECT Left([Name],InStr([name],",")-1) AS FirstName,
    Right([name],InStr([name],",")+1) AS LastName
    FROM Table1;

    and export the results to Excel.
    Hi and thank you both for your solutions. The TextToColumns worked very well. I did however try the Query one with weird results. When it pulled out the names to the Right of the comma it carried over a few letters from the left of the comma. Funny thing is it did so inconsistently. Meaning, in some cases it brought over one character, other times it brought over two characters. And that being with the name on the left being of varied lengths. I thought it was because they were too long, in some instances the name on the left had 4 characters and sometimes one character was carried over to the right and other times it didn't. Any explanation for that kinda strange thing? Oh, I am using Access/Excel 2K.
    Mike, do I just create a module in Excel and place that in there and it knows where the data is to do what needs to be done? Not familiar with what you showed me which might be great also. Glad to learn more than one way to skin a cat. Again, thanks to the both of you.

    BUD

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    bzzzt...wrong answer...see below
    Last edited by RedNeckGeek; 01-25-07 at 10:59.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Bud
    Mike, do I just create a module in Excel and place that in there and it knows where the data is to do what needs to be done? Not familiar with what you showed me which might be great also. Glad to learn more than one way to skin a cat. Again, thanks to the both of you.

    BUD
    I guess the question needs to be asked: How are you getting the names? A query? Direct import?

    What I wrote could be used in processing a recordset ... Not really in say a query directly... As for where to put it? a Module. Make a public wrapper function ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    duh...
    I left out a minor part of the query...
    Try this instead:

    SELECT Left([Name],InStr([name],",")-1) AS FirstName, Right([name],Len([Name])-InStr([name],",")) AS LastName
    FROM Table1;


    Mike,
    My computer gives me a WTF for SPLIT in both Access and Excel???
    Nothing in the help files about it, either.
    Are you saying that Bud should create a function called Split to parse the
    field for him? Or is that some Excel add-on that I haven't installed?
    Inspiration Through Fermentation

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by RedNeckGeek
    duh...
    I left out a minor part of the query...
    Try this instead:

    SELECT Left([Name],InStr([name],",")-1) AS FirstName, Right([name],Len([Name])-InStr([name],",")) AS LastName
    FROM Table1;


    Mike,
    My computer gives me a WTF for SPLIT in both Access and Excel???
    Nothing in the help files about it, either.
    Are you saying that Bud should create a function called Split to parse the
    field for him? Or is that some Excel add-on that I haven't installed?
    Split is a standard VB/VBA function ... It should be available in Access, VB, and Excel ... Sounds like something is arfed on your machine ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Wink

    Quote Originally Posted by M Owen
    Split is a standard VB/VBA function ... It should be available in Access, VB, and Excel ... Sounds like something is arfed on your machine ...
    The Text to Columns worked well and just stuck with that being as they are importing the data into Excel. So I just do it there and then pull it into Access from there. Thanks for very much for all your assistance. I'm gonna have to learn about Arrays and stuff someday soon.

    have a nice one,
    BUD

Posting Permissions

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