Splitting a Cell (no useable delimiter).....

[Puggy]

Hi,

Say I have a sheet with column A as a list of peoples names, only there is no space or comma etc, though use of capitals is correct eg:

FredBloggs
JasonGass
SteveKyte

etc etc etc.

Is it possible to use VB, or a function to split the name into firstname in say column B and surname in Column C?

Thinking of in some way pulling it out using the 2nd uppercase letter, but not sure how?

Thanks
Neil

[jindon]

Assuming data starts from A1

Sub SplitName()
Dim i As Integer, ii As Integer, a(), r As Range, txt
For Each r In Range("a1", Range("a65536").End(xlUp))
    ReDim Preserve a(1 To 1): a(1) = 1: ii = 1
    For i = 2 To Len(r)
        If StrComp(Mid(r, i, 1), UCase(Mid(r, i, 1)), vbBinaryCompare) = 0 Then
            ii = ii + 1: ReDim Preserve a(1 To ii): a(ii) = i
        End If
    Next
    For i = LBound(a) To UBound(a)
        If i = UBound(a) Then
            txt = txt & "," & Mid(r, a(i), Len(r) - a(i) + 1)
        Else
            txt = txt & "," & Mid(r, a(i), a(i + 1) - a(i))
        End If
    Next
    txt = Right(txt, Len(txt) - 1): txt = Split(txt, ",")
    r.Offset(, 1).Resize(, UBound(txt) + 1).Value = txt
    Erase a: txt = ""
Next
End Sub

[Puggy]

You are a star sir Thanks very much