how to change middle name to middle initial

[dbwiz]

I have a long list of names on spreadsheet tbb I am trying to match up with a separate spreadsheet of physician names called WM. Most failed to match, and it is because WM only has a middle initial, while tbb often had a whole middle name. I need to be able to search for and replace any middle names on tbb to be just a middle initial so that

Lastname, Firstname Middlename

is changed to

Lastname, Firstname MI

For example: WM might have Doe, Jane Eyre MD
tbb has Doe, Jane E MD

How can I do this? I know there is a Find and a Left in there...help?

 

[GTO]

Presuming Lastname, Firstname Middlename is in one cell, odd spacing, last names of more than one word (like Smith-Wellington) and/or missing middle names and such can be a pain. I would suggest looking through your list, finding the oddest examples and posting these as sample data.

 

[dbwiz]

There is a last name then a comma then a first name (all of these are consistent), then a space. And you are correct, after that it varies. I may have one middle name, a few even have two and then the designation or title with only spaces.

I am probably just going to have to break them all out doing text to columns and then splice back together. How can I change one word to be just first initial?

Assume A1 has Eugene
B1 has Antonito
C1 has Karen

How can I change those to just be
E
A
K

 

[LxQ]

=left(a1,1)

 

[dbwiz]

Thanks! Knew it was simple. I was hoping for a one step does all, but this is probably a lot easier.

 

[dbwiz]

OK, I did text to columns and split it apart. Now I am ready to concatenate but it will not do it. It just sits there in the cell with the formula in it even though there are no errors when I use the formula it shows Ainsworth, Victoria. I checked - the cells all appear to be text. What in the world am I missing?

Excel Workbook
	N	O	P
2	AINSWORTH,	VICTORIA	=CONCATENATE(N2," ",O2)
R0257144

Excel 2007

 

[LxQ]

I usually use a simple =N2&" "&O2
you might have the P2 cell set to text or something.. just set it to general, it will put a text in it automatically..