Split up a name filed into to separate fields. 3

[allenEd]

Hi,

I have a database which has 70000 records with a "name" field which consists of first name and surname. Can anyone help me to split this up in two new fields "FirstName" and "Surname"?

Just to be able to do this would be good, but....to make it just that little bit easier, someone has entered Mr or Mrs and also single letter middle initials in some of the original fields.

any help would be greatly recieved,

thanks
Allen.

 

[vbajock]

Look at the split function in the help file. If you search this forum using split as a keyword, you will probably find a million posts on this

 

[allenEd]

thanks vbajock, I will have a look.

 

[Parax]

Look for the position of the first space then show all chars to the left:

forename:Trim(left(Name,(instr(Name," &quot)))

Or Right:

Surname:Trim(Right(Name,(Len(Name)-instr(Name," &quot))))


This fails if there is no space!! so apply a filter and deal with those seperatly.

 

[BSman]

Here's the basic process. The assumption is that there is a space between the first and last names.

You can probably do it with a query that is something like this:

The name of the table here is NameSplitTest, [FullName] is the column for the full name, [FName] for the first name, and [LName] is for the last name.

UPDATE NameSplitTest SET NameSplitTest.FName = Left$([NameSplitTest]![FullName],InStr(1,[NameSplitTest]![FullName]," &quot), NameSplitTest.LName = LTrim(Mid$([NameSplitTest]![FullName],InStr(1,[NameSplitTest]![FullName]," &quot,40));

To use this go into the query builder, select the SQL display, paste this statement into the SQL display, then change the names of the table and the columns to agree with your database.


The way this works is the first name is found by taking the first x characters of the full name, where x is the location of the first space in the name. The last name starts at the location of the first blank and goes to the end of the name. I used 40 characters only because that should be more than enough to take the longest last name.

The LTrim function trims any leading spaces from the last name, so whether there is one or two spaces between the names, LName will end up with no spaces in front of the start of the last name.

Note that this will also handle last names that include a space (like, "Mac Dougal" for example). But it will not properly handle a full name that includes two first names unless they are separated by a dash rather than a space.

 

[pbcharlie]

BSman, thats a great picec of SQL, Thanks Thanks in advance
Charlie

 

[allenEd]

Thanks BSman
Allen