Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Separating Names (XP)

    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.

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating Names (XP)

    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.
    <pre>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
    </pre>


  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separating Names (XP)

    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...

  4. #4
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating Names (XP)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating Names (XP)

    Thanks, i will give it a try.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating Names (XP)

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

  7. #7
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating Names (XP)

    <P ID="edit" class=small>(Edited by mbarron on 19-Feb-09 14:27. better explanation of the 99 - I hope)</P>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,<font color=blue> len(cCell)-i+1</font color=blue>)


    but 99 is much easier to remember.

  8. #8
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating Names (XP)

    Thanks. Let's hope that someone does not have a very long first name <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> And you explanation is very clear.

  9. #9
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating Names (XP)

    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))
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  10. #10
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating Names (XP)

    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.

  11. #11
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating Names (XP)

    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.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •