Code for dividing name into names

[Olavxxx]

Hi,

I think at some point, most of the VBA programmers have to divide a name string, into multiple names.. eg. firstname and lastname.

I programmed this simple, but yet, needed function.
Since this is something I think people might need, I thought I could share the code with you.

ok, here goes:

Function findName(sBigString As String)
    
    ' lets initialize the variables
    Dim iPositionOfCharacter As Integer ' pos of needle
    Dim sSubString As String 'search for?
    
    sSubString = " " ' search for space (" ")
    
    iPositionOfCharacter = InStrRev(sBigString, sSubString) ' search for the needle in the haystack
    If iPositionOfCharacter <> 0 Then ' if it was found
        firstNam = Left(sBigString, iPositionOfCharacter - 1) ' get the fnam
        lastNam = Mid(sBigString, iPositionOfCharacter + 1) ' get the lnam
    Else ' if not found
        firstNam = sBigString ' set fnam to entire nam
        lastNam = "-mangler-" ' set lnam to "-mangler-"
    End If
    
End Function

I guess this code could be further optimized, if it's needed to be more dynamic. My variable for needle, could might as well be a constant. I guess it could be an optional pass-thru variable inside the function scope?

I however need it as it is, but I guess people can modify it to theire needs, as it's not very complex at all.

I found you can not use right() on the lastNam, as then it sometimes will start at wrong points in the variable.

I first tried working around this, but found it easier to stick with mid() and left()

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[Olavxxx]

btw. define the firstNam and lastNam as global variables!

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[lewds]

Hi..

Have you considered the use of Split()?

Dim B as Variant
Dim sBigString, fnam, lnam as String
B = Array(2)
B = Split(sBigString, " ")
fnam = B(0)
lnam = B(1)

 

[MichaelRed]

Have you considered the multiplicity of the 'design' approach to the storage, the variation of 'names in general (middle initial or initials or middle name(s), wheather the prefix (Mr., Mrs., Ms., Dr., etc are stored with the name), wheather the individual has a formal title (Jr., III, President, etc) might be included, and then simply the vagaries of data entry.

I have attempted on several occassions to 'groom' name lists into a 'formal' pattern, and encountered many more variations than listed here. Even the smaller lists generally require a thorough manual review, generally having a 20% (or higher) 'failure rate', even when parsed with as many 'variations' accounted for as I could reasonably incorporate.





MichaelRed

 

[Olavxxx]

Hi,
In Norway, we dont have mr., ms, mrs, dr., etc.

I use this very easy:
look for " " backwards, if not found, this means user has no last name. (eg error on input).

If user has it, make the first part the first name (w. middle name), and the last name the rest.

I dont think split is good, since it may not always have the data there.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[DougStevens]

I've written several functions like these. It almost always depends upon the nature of the data set that determines the best approach.

However, my favorite function involved passing the full name as a string and a coded valued to return the part of the name that I wanted. (e.g. 1=FirstName, 2=MiddleName, 3=LastName, 4=Suffix).

FYI, Most of the data sets that I encountered did not have a salutation or title as part of their names. But those strings can be usually tested for.

Here's some psuedo code of a similiar function:
Function GetNamePart (GivenName as String, NamePart as Integer) As String

Dim FirstName As String
Dim MiddleName As String
Dim LastName As String
Dim Suffix As String
'You can dim a title or salutation as well.
Dim NameArray

NameArray = Split(GivenName, " ")
IF InStr("Hello,JR,SR,II,III", NameArray(UBound(NameArray)) THEN
'We've found a Suffix!!!
'Adjust the size of the array accordingly and assign the Suffix variable with the value.
END IF

'Then depending on the number of remaing elements in the array, we can make some assumptions about the name.
SELECT CASE UBound(NameArray)
CASE 1
'One name only, assume its last name
CASE 2
'First and Last names
CASE 3
'First, Middle and Last Names
CASE 4
'First, Middle, and two words for the last name
'This is where it is tricky and the code is weak
CASE ELSE
'you can decide what to do.
END SELECT

'Now Return the value you wanted
SELECT CASE NamePart
CASE 1
GetNamePart = FirstName
CASE 2
GetNamePart = MiddleName
CASE 3
GetNamePart = LastName
CASE 4
GetNamePart = Suffix
CASE ELSE
'Opps, just return the whole name I guess
END SELECT

END Function



Now this function can be used inside a query, form or report.

 

[Olavxxx]

DougStevens: I guess your way is better for international usage, but my way is just used for an insert query, where db1.table1.field1 has to be inserted in db2.table1.field1 and db2.table1.field2.

Also db1.table1.field2 has to be inserted in db2.table2.field1

I think that I should not make my project more advanced than it has to be, as it should only provide me with the simple task of extracting the " ..." from the end of the string.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster