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

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

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