Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What is in a Name (XL97:SR2)

    Is to possible to create a formula to separate last and first names? Where there are no space or commas between the last and first name...

    Example:
    DoeJohn
    MooseBullwinkle

    I would like to include a comma between the last and first name. As you can see the length of the name changes and the only distinction between the last and first name is the capital letter of the first name.

    Thanks,
    John

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What is in a Name (XL97:SR2)

    Yes, but it will take an array formula which looks for the second capital letter. No time to do it now: makes my hear hurt! Search in the Excel lounge for Array formula and see if you can get started.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What is in a Name (XL97:SR2)

    The VBA routine below should do that for all of the cells in the current selection:

    <pre>Public Sub FixNames()
    Dim oCell As Range
    Dim I As Long
    For Each oCell In Selection
    If oCell.Value <> "" Then
    For I = 2 To Len(oCell.Value)
    If Mid(oCell.Value, I, 1) = UCase(Mid(oCell.Value, I, 1)) Then Exit For
    Next I
    oCell.Value = Left(oCell.Value, I - 1) & ", " & Right(oCell.Value, Len(oCell.Value) - I + 1)
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What is in a Name (XL97:SR2)

    Legare,

    The code works like a charm.

    Thanks,
    John

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What is in a Name (XL97:SR2)

    No, VBA is cheating <img src=/S/scold.gif border=0 alt=scold width=50 height=15> <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15> Where's <img src=/S/doctor.gif border=0 alt=doctor width=25 height=33> Bob Umlas when you need him <img src=/S/question.gif border=0 alt=question width=15 height=15>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What is in a Name (XL97:SR2)

    OK, OK. Assuming JohnDoe, etc is in A1 (and you can fill these formulas down)
    First name array enter:
    =LEFT(A1,MATCH(TRUE,CODE(MID(A1,ROW($2:$20),1))<91 ,0))
    2nd name, array-enter:
    =MID(A1,MATCH(TRUE,CODE(MID(A1,ROW($2:$20),1))<91, 0)+1,255)

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What is in a Name (XL97:SR2)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Great job, Bob! I placed your formulas into the attached worksheet for the doubters. BTW, I noticed that if you insert a row before the formulas, everything gets messed up. I understand why (Excel thinks that it must relocate $2:$20), but was wondering if there was a way to fix it?
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What is in a Name (XL97:SR2)

    Sure:
    =LEFT(A1,MATCH(TRUE,CODE(MID(A1,ROW(INDIRECT("$2:$ 20")),1))<91,0))
    etc.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What is in a Name (XL97:SR2)

    No, array fromulas are too much work. You have to create the formula and then copy and paste special to get the names back without the formulas, then delete the formulas. Thats why lazy people are the best programmers.
    Legare Coleman

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What is in a Name (XL97:SR2)

    > lazy people are the best programmers
    <img src=/S/yep.gif border=0 alt=yep width=15 height=15> If I had a quarter for each time my wife said, "You could have typed it from scratch be now!", I'd be a rich man! And I agree that the VBA solution is the way to go, but, remember, John asked, "Is to possible to create a formula! Besides Array formulas are much more challenging: how often can you write an entire program on just one line?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What is in a Name (XL97:SR2)

    > INDIRECT
    Well, duh, why didn't I think of that! Thanks!
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: What is in a Name (XL97:SR2)

    Sammy,

    I guess Bob left it as an exercise - INDIRECT has to be applied to both formulas. Maybe that was obvious to others but not me when I applied it at first.

    Fred

Posting Permissions

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