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
Parse String
-
- 3StarLounger
- Posts: 253
- Joined: 19 Apr 2010, 09:01
- Location: Indiana, USA
Parse String
Thanks,
Mark
Mark
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Parse String
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
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
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 253
- Joined: 19 Apr 2010, 09:01
- Location: Indiana, USA
-
- Administrator
- Posts: 12602
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Parse String
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.
StuartR
-
- Administrator
- Posts: 78420
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Parse String
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.".
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 482
- Joined: 24 Jan 2010, 15:02
- Location: Colorado, USA
Re: Parse String
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.
Wendell
You can't see the view if you don't climb the mountain!
You can't see the view if you don't climb the mountain!
-
- Administrator
- Posts: 78420
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Parse String
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)
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)
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Parse String
I know this is an Access/SQL thread, but if anyone is intersted, here are Excel formulas to split:HansV wrote: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)
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," ",""))))))
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.