Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    23

    Exclamation Unanswered: How to Split middle initial from the last name

    HI
    I have a excel spreadsheet where a column carries 600 users where one column carries first name and other last name other etc. In the last name column, it shows user last name with user middle name in the Capital latter.
    Is there a way to separate the middile initial from the last name.

    the original spreedsheet:
    1 LisaM
    2 JohnC
    3 AlexC.
    4 MichealS
    5 SchwartzeR

    Desire result:
    1 Lisa M
    2 John C
    3 Alex C
    4 Micheal S
    5 Schwartze R

    Thanks!

  2. #2
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by arandomfrog
    HI
    I have a excel spreadsheet where a column carries 600 users where one column carries first name and other last name other etc. In the last name column, it shows user last name with user middle name in the Capital latter.
    Is there a way to separate the middile initial from the last name.

    the original spreedsheet:
    1 LisaM
    2 JohnC
    3 AlexC.
    4 MichealS
    5 SchwartzeR

    Desire result:
    1 Lisa M
    2 John C
    3 Alex C
    4 Micheal S
    5 Schwartze R

    Thanks!
    Sorry, i am not very good in excel formula, especially i found it hard to debug in formula (can anyone suggest a way to debug a nested formula?), thats why i always solve problem by using vba.

    For this given example, it takes me only 1 mins to write the code in vba, but it will probably take me 1 hour to find which formula to do the task (my prediction). However, someone might be good in using formula which can solve this task by using a single line of formula which i admire alot.

    Here is a button i created to do this task. Try it out.

    Just put all the name on column A, this press the button and your answer will be listed on column B. The computer will assume all the name will have initial in capital letter at last. if you have a name like LisaMa, u will get Lisa Ma
    Attached Files Attached Files
    Last edited by mkggoh; 07-18-06 at 22:10.

  3. #3
    Join Date
    Jun 2006
    Posts
    103
    for your convience if you know how to make a command button, below is the code for the button called cmdFilter (it will be same as the excel file that i previously posted)

    Have fun

    Private Sub cmdFilter_Click()
    Dim lngLastRow As String
    Dim checkChar As String

    lngLastRow = ActiveSheet.Range("A65536").End(xlUp).Row

    For counter = 1 To lngLastRow
    checkChar = Mid(ActiveSheet.Range("A" & counter).Value, Len(ActiveSheet.Range("A" & counter).Value), Len(ActiveSheet.Range("A" & counter).Value) - 1)
    If Asc(checkChar) >= 65 And Asc(checkChar) <= 90 Then
    ActiveSheet.Range("B" & counter).Value = Mid(ActiveSheet.Range("A" & counter).Value, 1, Len(ActiveSheet.Range("A" & counter).Value) - 1) & " " & checkChar
    End If
    Next
    End Sub

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Quote Originally Posted by arandomfrog
    HI
    I have a excel spreadsheet where a column carries 600 users where one column carries first name and other last name other etc. In the last name column, it shows user last name with user middle name in the Capital latter.
    Is there a way to separate the middile initial from the last name.

    the original spreedsheet:
    1 LisaM
    2 JohnC
    3 AlexC.
    4 MichealS
    5 SchwartzeR

    Desire result:
    1 Lisa M
    2 John C
    3 Alex C
    4 Micheal S
    5 Schwartze R

    Thanks!
    As long as there isn't a period after an occasional one:

    =LEFT(B2,LEN(B2)-1)

    However, this takes care of the case of an occasional period:

    =IF(RIGHT(B2,1)=".",LEFT(B2,LEN(B2)-2),LEFT(B2,LEN(B2)-1))
    Last edited by shades; 07-19-06 at 15:37.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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