Removing middle name/initial from a cell!

[jesswoodm]

I have a database that I created with First Name, Initial/Middle Name, and Last Name (all in one cell).

I am now trying to update this database, but the new records I am trying to insert are only First Name, Last Name (all in one cell).

I want to change my existing database to only have First Name, Last Name in the same cell. Also, I want to cross reference my new cells with the existing database in order to ensure I have not duplicated by having an initial in one of the cells.

I have basic knowledge of macros and formatting etc and have played around with several potential solutions, but I'm not having much luck. I managed to separate the existing database cells, so now I have one column which contains either just the first name, or first name last name, depending on whether the person had a middle initial entered.

Please help!

 

[nightcrawler23]

Try this formula. assume your names are in A1

=TRIM(LEFT(A1,FIND(" ",LOWER(A1),1))) & " " & TRIM(MID(A1,FIND(" ",LOWER(A1),FIND(" ",LOWER(A1),1)+1)+1,LEN(A1)-FIND(" ",LOWER(A1),1)+1))

 

[jesswoodm]

Thanks for your help. However, I am unsure where this goes. I got my whole list, complete with first name, initial and last name in A1:A4400. Then I pasted the cell into B1. Is this the right place? It came up with an error, and the same when I tried to use it as a macro.

Any further help would be really appreciated!

 

[Michael M]

Hi and welcome to the Board
This was posted previously by someone who I can't give credit to...but I reckon it's brilliant

Try
Highlight the column in question and then
Edit > Replace with Find: ' * ' and Replace: ' '
So, Find SPACE followd by * followed by SPACE

Replace SINGLE Space

 

[nightcrawler23]

it works for me though. Can you give an example where this formula gives an error

 

[Michael M]

If it's any consolation...your formula works fine for me !

 

[jesswoodm]

I'm sure it does work, I just don't have the knowledge to make this happen.

I put my list in Column A, rows 1-4400. Then I pasted

=TRIM(LEFT(A1,FIND(" ",LOWER(A1),1))) & " " & TRIM(MID(A1,FIND(" ",LOWER(A1),FIND(" ",LOWER(A1),1)+1)+1,LEN(A1)-FIND(" ",LOWER(A1),1)+1))

into B1. It then says #VALUE, and gives me this when I click on 'more info':


Correct a #VALUE! error
Occurs when the wrong type of argument or operand is used.

Click the cell that displays the error, click the button that appears , and then click Trace Error if it appears.
Review the possible causes and solutions




Am I doing something wrong? Thanks for your help.

 

[Rick Rothstein]

Does this formula do what you want?

=LEFT(A1,FIND(" ",A1))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

 

[jesswoodm]

Rick, that does EXACTLY what I want. Thank you so much.

Thanks to everyone for their help.

 

[Michael M]

As a matter of curiosity, did you try the FIND / REPLACE method.
I'd be interested in feedback, mainly from the point of when NOT to use it, or when it's not suitable