Splitting Up Words

[Ricky1]

How would i do this.

my names keep getting returned as

StephenBrown
SimonSmith
NickScott

and i want them to be

Stephen Brown
Simon Smith
Nick Scott

so how would i make VB but a space b4 the second capped letter ?

[Bruce Fox]

How are you gettin those names in the first place?

I ask, because you may be able to add a space whilst concatenating (joining) the first and last name

[Ricky1]

sorry probably wasnt clear the program to get the names it coded by me. Someone has just asked me to make a prog to split the names up.

But i didn't know where to start of if its even possible.

[jcis]

This separates all subnames (even more than 2) that start with upper case:

Private Function GetName(pName As String) As String
Dim i     As Integer
Dim lName As String
    
    For i = 2 To Len(pName)
        If Mid$(pName, i, 1) = UCase(Mid$(pName, i, 1)) Then
            pName = Left$(pName, i - 1) & " " & Right$(pName, Len(pName) + 1 - i)
            i = i + 1
        End If
    Next
    GetName = pName
End Function

Anyhow, it would do things much slower, as Bruce said, it would be better separate the fields before.

[ZeBula8]

Using the constants I found on MSDN and I used collections to parse out the pieces of a passed in name -

It's not totally fool-proof (J. R. R. Tolkien, for example will not parse correctly, but other common name combinations will. ie: Mr & mrs john m smith; Joe Smith; Joe W. Smith, Jr; etc)

Option Explicit
 
 
Const Pedigrees = "Jr.Sr.III,IV,VIII,IX,XIII"
 
' dim these so the ParseIt method will return by reference the values
Dim mtitle As String, ftitle As String, sfirstname As String
Dim smi As String, slastname As String, ssuffix As String
 
Sub Command1_Click()
 
' txtName textbox for testing
ParseIt txtName, mtitle, ftitle, sfirstname, smi, slastname, ssuffix
 
 
' show results
Debug.Print mtitle
Debug.Print ftitle
Debug.Print sfirstname
Debug.Print smi
Debug.Print slastname
Debug.Print ssuffix
 
 
End Sub
Private Function ParseIt(s As String, mtitle As String, ftitle As String, sfirstname As String, _
    smi As String, slastname As String, ssuffix As String)
 
Dim mArray As Variant
Dim fArray As Variant
Dim v As Variant
Dim bmTitleFound As Boolean
Dim fmTitleFound As Boolean
Dim b2in1 As Boolean
Dim ix As Long
Dim ij As Long
 
Dim lCount As Long
 
 
' default values
mtitle = vbNullString
ftitle = vbNullString
sfirstname = vbNullString
smi = vbNullString
slastname = vbNullString
ssuffix = vbNullString
 
 
' split name into an array
v = Split(s, " ")
 
If IsEmpty(v) Then Exit Function
 
Dim mcol As Collection
Set mcol = New Collection
 
If IsArray(v) Then
    ' load into a collection
    For ix = LBound(v) To UBound(v)
        If v(ix) <> "" Then
            mcol.Add v(ix), CStr(ix)
        End If
    Next
End If
 
' build array
mArray = Array("Mr", "Mr.")
fArray = Array("Mrs", "Mrs.", "Miss", "Ms", "Ms.")
 
' check for a title
'For ix = 1 To mcol.Count
lCount = mcol.Count
If lCount > 0 Then
    For ix = 1 To lCount 'mcol.Count
        
        If ix > lCount Then Exit For
        
        If Not bmTitleFound Then
        For ij = LBound(mArray) To UBound(mArray)
            If StrComp(mcol.Item(ix), mArray(ij), vbTextCompare) = 0 Then
                mtitle = mcol.Item(ix)
                mcol.Remove ix
                bmTitleFound = True
                lCount = lCount - 1
                Exit For
            End If
        Next
        End If
        
        If Not fmTitleFound Then
        For ij = LBound(fArray) To UBound(fArray)
            If StrComp(mcol.Item(ix), fArray(ij), vbTextCompare) = 0 Then
                ftitle = mcol.Item(ix)
                mcol.Remove ix
                fmTitleFound = True
                lCount = lCount - 1
                Exit For
            End If
        Next
        End If
    Next
    
    ' remove ampersand if exists
    If mcol.Count > 0 Then
        For ix = 1 To mcol.Count
            If InStr(mcol.Item(ix), "&") > 0 Then
                mcol.Remove ix
                b2in1 = True
                Exit For
            End If
        Next
    End If
    
    ' get first name
    If mcol.Count > 0 Then
        sfirstname = mcol.Item(1): mcol.Remove 1
    End If
    
    ' get suffix
    If mcol.Count > 0 Then
        For ix = 1 To mcol.Count
            If InStr(1, Pedigrees, mcol.Item(ix), vbTextCompare) > 0 Then
                ssuffix = mcol.Item(ix)
                mcol.Remove ix
                Exit For
            End If
        Next
    End If
    
    ' get last name
    If mcol.Count > 0 Then
        slastname = mcol.Item(mcol.Count): mcol.Remove mcol.Count
        slastname = Replace(slastname, ",", "")
    End If
    
    ' check for middle initial/name
    If mcol.Count > 0 Then
        smi = mcol.Item(1)
    End If
End If
 
End Function

[Bruce Fox]

ZeBula8,

Unfortunatly I don't think that will work as is. The passed Name (s) dosn't have a space (the deliniator your using with the Split()), so the function will fall thru each time.

[ZeBula8]

Right, Bruce -

I totally misread the original question -

[Bruce Fox]

I'm sure it will still come in handy

[dglienna]

Names are very hard to split, as they can have too many variations. best to have 4 different fields. That way you know if the first name has a space in it, and the last name has 2 spaces, and then have a jr/sr suffix. Your split could have up to 10 indexes.