Parse String

[santosm]

Hi All,
I have a field that contains a persons full name (John Doe). I want to split the name into two fields. I am thinking that I can evaluate the string from the left to right, wait until a space is seen, and then select the preceding characters to split off. How do you do that???

Thanks,
Mark

[Rudi]

Hi Mark,

You can use two formulas in a query to do this:
Add the table containing the name field to a new query
Add the name field to the design grid

In the second column of the design grid add this formula:
First Name: Left([Name],InStr([Name]," ")-1)

In the third field, add this formula.
Last Name: Right([Name],Len([Name])-InStrRev([Name]," "))

Note: Replace [Name] in the expressions above with the field name of the field in question.

You can convert the query into an update query if you want to update the actual field in the table...

Here is an interesting article on this process too... : Splitting a Name Field into First and Last Name

[santosm]

Thanks Rudi!

[StuartR]

This assumes that every name in your database is of the form "Firstname Lastname", it's going to do strange things if you have other things line "Mr Wendell Jones", "Steven L. Jones" etc.

[HansV]

Rudi's expressions will work for names such as "Steven L. Jones" or "William Henry Dubois", but it'll fail for names with with a prefix such as "Mr." or "Dr." or a suffix such as "Jr." or "Ph.D.".

[Wendell]

There are also issues with last names where there is a prefix such as Mc or de - which is why we insist on storing names in separate fields, and assemble them with code when we want the full name.

[HansV]

I agree - it's always best to store the parts of names (title, first name, middle name/initial, last name, suffix) in separate fields. Same for addresses.
But if you receive an existing data file with names in a single field, the above expressions can help splitting the field into several fields, although in general some 'manual' cleaning up will be required. (I tend to use Excel for name/address cleaning)

[Rudi]

I agree - it's always best to store the parts of names (title, first name, middle name/initial, last name, suffix) in separate fields. Same for addresses.
But if you receive an existing data file with names in a single field, the above expressions can help splitting the field into several fields, although in general some 'manual' cleaning up will be required. (I tend to use Excel for name/address cleaning)

I know this is an Access/SQL thread, but if anyone is intersted, here are Excel formulas to split:

Name and Surname: (if the single column has first the name, then the surname...)

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

Surname Formula (any one of these variations)
=TRIM(MID(A2,FIND(" ",A2)+1,LEN(A2)))
=RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) ~OR~
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

Or

Surname and Name: (if the single column has first the surname, then the name...)

Name Formula
=TRIM(LEFT(A13,FIND(CHAR(3),SUBSTITUTE(A13," ",CHAR(3),LEN(A13)-LEN(SUBSTITUTE(A13," ",""))))-1))

Surname Formula
=TRIM(RIGHT(A13,LEN(A13)-FIND(CHAR(3),SUBSTITUTE(A13," ",CHAR(3),LEN(A13)-LEN(SUBSTITUTE(A13," ",""))))))