# Thread: Separating two names into two fields (97, 2000)

1. ## Separating two names into two fields (97, 2000)

I have a field with two names in it (last then first separated by a space) that I want to separate into two fields. How do I do it? It would be particularly useful if I could show the last string, then everything except the last string. Then somebody like Van der Wall Marie would end up with Marie in the FirstName field and Van der Wall in the LastName field.

2. ## Re: Separating two names into two fields (97, 2000)

You said a field with two names, but obviously you didn't mean two *words*. Are the names always in last name then first name order? If so, you can search for the last space and show everything up to that as the last name. However, what happens if you have a name like Van der Wall Anne Marie?

Here's a routine that will extract the last word from a string, in this case the First name, and an example of how to use it for this purpose
<pre>Public Function ExtractLastValue(ByVal varList As Variant, _
Optional strSep As String) As Variant
'created 3/14/2000 by C Foust
Dim strLast As String
Dim intBegPos As Integer
Dim intEndPos As Integer

If strSep = "" Then
strSep = ";"
End If
intBegPos = 0
intEndPos = InStr(2, varList, strSep)
Do
If intEndPos > 0 Then
If intBegPos = intEndPos Then
intBegPos = intEndPos + 1
Else
intBegPos = intEndPos
End If
End If
intEndPos = InStr(intBegPos, varList, strSep)
Loop Until intEndPos = 0
strLast = Right(varList, Len(varList) - (intBegPos - 1))
ExtractLastValue = strLast
End Function</pre>

<pre>Public Function TestExtractLastName()
Dim strName As String
Dim strLastName As String
Dim strFirstName As String

strName = "Van der Wall Marie"
strFirstName = ExtractLastValue(strName, " ")
strLastName = Left(strName, InStr(strName, strFirstName) - 1)

TestExtractLastName = strLastName
End Function</pre>

3. ## Re: Separating two names into two fields (97, 2000)

Thanks, John. I looked at the modules. Since I don't know Visual Basic, they were much too complicated for me.

4. ## Re: Separating two names into two fields (97, 2000)

The database of names is not very large and only has a couple of exceptions, so if I can separate the two names that appear in all but three records, that would be great and I can do these three manually.

So to make things simple, I am looking for a simple way of separating two words in a field into two separate fields.

John

5. ## Re: Separating two names into two fields (97, 2000)

I believe Wendell suggested in the last month or two a method that is very simple. Export the data to Excel and choose Data menu then text to columns and follow the wizard, then re-import the data into your database. Quick, simple and little hassle.

Peter N

6. ## Re: Separating two names into two fields (97, 2000)

(Edited by HansV to activate URL - see <!help=19>Help 19<!/help>)

Hi John

Go to Google.com and search for neatcode2.mdb

Look at Module String Manipulation/Parsing to get you started

http://support.microsoft.com/?kbid=148287

HTH

John

7. ## Re: Separating two names into two fields (97, 2000)

Thank you for the suggestion. For what I have in mind, it's just the ticket.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•