# Thread: How to Split middle initial from the last name

1. Registered User
Join Date
Jan 2006
Posts
23

## 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. Registered User
Join Date
Jun 2006
Posts
103
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
Last edited by mkggoh; 07-18-06 at 21:10.

3. Registered User
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. Registered User
Join Date
Oct 2003
Posts
1,091
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 14:37.

#### Posting Permissions

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