What is in a Name (XL97:SR2)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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)

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

> INDIRECT
Well, duh, why didn’t I think of that! Thanks!

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

Legare,

The code works like a charm.

Thanks,
John

Reply | Quote

No, VBA is cheating Where’s Bob Umlas when you need him

Reply | Quote

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.

Reply | Quote

> lazy people are the best programmers
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?

Reply | Quote