Separate Family Name from First Name

Hey everyone,

I need some simple string functions to locate the first space in a given string and separate the string before the space from the string after it.

this is for separating a list of full names into 2 lists of first names and last names.

Thanks in advance,

why not make use of the split() function that splits a string up based on a certain delimiter (a space?)
it returns an array indexed based on the delimiting character.

Here is a sample

VB Code:

Option Explicit
 
Private Sub Form_Load()
  Dim str As String, vName() As String
  str = "Sam Spade"
  vName() = Split(str)
  MsgBox vName(1) & " " & vName(0)
End Sub

You might want to check Ubound(vname) to make sure that there aren't two last names, or a Jr, or Sr. If so, add it to the last name.

Splitting names is nearly impossible unless there are some strict rules about what the name can contain. Jr and Sr are one difficulty but you could also run into something like J. R. R. Tolkien.

We inforce strict rules on data entry of names.

LASTNAME, FIRSTNAME MIDDLENAME, SUFFIX

such as: "SMITH, JOHN ROBERT, JR"

We then can look for the commas to find the various parts we want to find.

If all your names have only one space then you are lucky - if the names you have look anything like the names we have encountered - it's not an easy task...

Personally - I would use INSTR to split a string into two pieces based on a space in the string. Using SPLIT with ARRAY's seems excessive to me.

Splitting on the first space is ok as long as your names are entered correctly and in the correct order. Also, no "Oscar Mayer Del La Hoya, Jr." type naming. :D

Quote:

---

Originally Posted by MartinLiss

Splitting names is nearly impossible unless there are some strict rules about what the name can contain. Jr and Sr are one difficulty but you could also run into something like J. R. R. Tolkien.

---

Very true. Parsing names is very difficult. There can be suffixes like Jr. Sr. There can be prefixes like Mr. Mrs. There can be middle names, there can be multiple last names.

The format of the name can come Lastname, Firstname. It is very challenging.

Thanks a lot, I'll give it a try.