Separating Names (XP)

Topic

New Reply

Exported a contact list from Outlook into Excel. Names in column A are in this format SmithBob. A variety of lengths in the names.Is there a method of using a function such as Mid to find the capital letter in the first name and separate the names? If no function, is there a method using VBA? Thanks.

Reply | Quote

Replies

Select the cells you wish to split and then run the following. It will put the characters before the second capital letter in the first column to the right of the selection and from the second capital two columns to the right.

Sub splitIt()
    Dim strFirst As String, strLast As String
    Dim cCell As Range, i As Integer


    For Each cCell In Selection
        For i = 2 To Len(cCell)
            If Asc(Mid(cCell, i, 1)) >= 65 And Asc(Mid(cCell, i, 1)) <= 90 Then
                strLast = Left(cCell, i - 1)
                strFirst = Mid(cCell, i, 99)
                cCell.Offset(0, 1) = strLast
                cCell.Offset(0, 2) = strFirst
            End If
        Next
    Next
End Sub

Reply | Quote

Thanks, i will give it a try.

Reply | Quote

Thanks, the coding worked. Just one question regarding strFirst = Mid(cCell, 1, 99), what does the 99 refer to?

Reply | Quote

(Edited by mbarron on 19-Feb-09 14:27. better explanation of the 99 – I hope)

99 refers to the number of characters following the second capital that should be taken into account.

mind function explained (very briefly)

mid(string,start_character,#_of_character)

It could be changed to:
strFirst = Mid(cCell, i, len(cCell)-i+1)

but 99 is much easier to remember.

Reply | Quote

Thanks. Let’s hope that someone does not have a very long first name And you explanation is very clear.

Reply | Quote

Are the names stored like that in Outlook? If I export contacts from Outlook to Excel, last name and first name are in separate columnns…

Reply | Quote

The names are in the format of last name, first name so why they are not being exported in that way is the reason for the question. Thanks.

Reply | Quote

One way, Non VBA solution

try this array formula, confirm by Ctrl,Shift and Enter

=LEFT(A1,MATCH(0,–EXACT(MID(A1,ROW(2:99),1),MID(LOWER(A1),ROW(2:99),1)),0))&” “&MID(A1,MATCH(0,–EXACT(MID(A1,ROW(2:99),1),MID(LOWER(A1),ROW(2:99),1)),0)+1,LEN(A1))

Reply | Quote

Unfortunately when I use your formula I get the following result.
SmithBob Smith Bob
BrownAlice Brow nAlice
TurnerDoug Turn erDoug
SharpMary Sh arpMary
WilsonDick Wi lsonDick
StevensSam St evensSam

Works fine for first name and then, well you see the results.

Reply | Quote

I have forgot about absolute references when replying to your post.

try this, confirm by Ctrl,Shift and Enter
=LEFT(A1,MATCH(0,–EXACT(MID(A1,ROW($2:$99),1),MID(LOWER(A1),ROW($2:$99),1)),0))&” “&MID(A1,MATCH(0,–EXACT(MID(A1,ROW($2:$99),1),MID(LOWER(A1),ROW($2:$99),1)),0)+1,LEN(A1))

This should return the results you want.

Reply | Quote