Reformat full name Columm to 2 columms-1st & Last Name 1

[kentwoodjean]

I have thousands of files from the 2 previous years that was originally imported into Access from Excel. The Name column was formated as follows: Last name, first name. I must now integrate this previous data into the text files we are now receiving. Those files are formated with 2 columns for the name: (1) Last name (2) First name.

Is there a simple way that I can reformat the 1 column data to become 2 columns, in another words the text following the comma should go to a newly added column.

Any help would be appreciated. the though of manually doing this causes me great pain. Thanks

 

[mgolla]

Try using this code:

Dim intLoc As Integer
Dim stTextBefore As String
Dim stTextAfter As String

intLoc = InStr(1, Text0, Chr(44))
stTextBefore = Left(Text0, intLoc - 1)
stTextAfter = Mid(Text0, intLoc + 2, Len(Text0) - (intLoc + 1))
Text0 = stTextBefore
Text2 = stTextAfter




HTH
Mike

 

[kubanvz]

Try these in query design view, assuming the existing name column is "Name"

Last name: Left([Name],(InStr([Name],",&quot-1))

First Name: Right([Name],(Len([Name])-InStr([Name],",&quot-1))

 

[cheerio]

The previous postings are on the right lines but I have a few observations.

First, I would separate at the comma and use a TRIM function to remove unwanted spaces spaces. The previous postings make assumptions about the format of the data that may be invalid. Good code should handle all these possibilities:

lastname,firstname
lastname_,_firstname
lastname,_firstname
lastname_,__firstname
lastname,___firstname
etc

where the underscore represents a space

Your process should simply get everything to the left of the comma, everything to the right of the comma and then trim off the spaces. Assuming there will always be one space after the comma is dangerous.

Secondly I would use a query not code - it's faster to develop and you can see the results. I would prove the logic with a select query and then convert to an update query. This way you can easily check that you are getting what you want. While developing I would limit the data returned to say 100 records.

Thirdly I would not touch the original name column - I would add new columns for lastname and firstname. Indeed ideally I would do this in a copy of the access database. Also, I would work on a local hard drive rather than a network drive.

Fourthly, given the volume of data I would run some queries to check the validity of your understanding of the data. For example, are there any records without a comma? Are there any records with two commas? Are there any records with a comma but nothing after it? Are there any with a comma that has nothing before it?

Finally, after conversion I would consider running a distinct query on the firstnames column to see the names that exist and look for anomalies. For example, this might pick up errors in your assumptions about the data that your other tests did not reveal.

 

[kentwoodjean]

Thanks for your advise. It worked perfectly.

 

[kentwoodjean]

Thanks Cheerio for the valuable input. I used the technique suggested by Kubanvz and it worked very well, meaning his assumptions regarding the format were correct.

I am saving your information to use with a couple of other columns I have to convert in databases this upcoming week. What I used for last name, first name will not work as well, and I will use your advice.