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

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.

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

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

bzzzt...wrong answer...see below

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 ...

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 ...

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