delete middle word from a cell

[Guest]

Good morning
I have a large spreadsheet. The entry in one columns has a name e.g. Mr.
John Smith. what I need is to delete the middle name so that it reads Mr.
Smith I tried the functions LEFT , RIGHT, and text to columns. what is the
right way to do this or do I need a macro?

 

[Guest]

Let's say you have Mr. John Smith in cell A1:

It's ugly, but it works

=LEFT(A1,SEARCH(" ",A1)-1)&" "&RIGHT(RIGHT(A1,LEN(A1)-SEARCH("
",A1)),LEN(RIGHT(A1,LEN(A1)-SEARCH(" ",A1)))-SEARCH("
",RIGHT(A1,LEN(A1)-SEARCH(" ",A1))))

 

[Lori]

Try Edit > Replace with Find: ' * ' and Replace: ' '
[Explicitly: Find: single space followed by * followed by single space;
Replace: single space]

 

[Guest]

Lori

PERFECT! thanks have agreat day

Try Edit > Replace with Find: ' * ' and Replace: ' '
[Explicitly: Find: single space followed by * followed by single space;
Replace: single space]

Good morning
I have a large spreadsheet. The entry in one columns has a name e.g. Mr.
John Smith. what I need is to delete the middle name so that it reads Mr.
Smith I tried the functions LEFT , RIGHT, and text to columns. what is the
right way to do this or do I need a macro?

 

[Raffael]

wow, that was brilliant!

Try Edit > Replace with Find: ' * ' and Replace: ' '
[Explicitly: Find: single space followed by * followed by single space;
Replace: single space]

Good morning
I have a large spreadsheet. The entry in one columns has a name e.g.
Mr.
John Smith. what I need is to delete the middle name so that it reads
Mr.
Smith I tried the functions LEFT , RIGHT, and text to columns. what is
the
right way to do this or do I need a macro?

 

[Lori]

Thanks - you too..

Lori

PERFECT! thanks have agreat day

Try Edit > Replace with Find: ' * ' and Replace: ' '
[Explicitly: Find: single space followed by * followed by single space;
Replace: single space]

Good morning
I have a large spreadsheet. The entry in one columns has a name e.g. Mr.
John Smith. what I need is to delete the middle name so that it reads Mr.
Smith I tried the functions LEFT , RIGHT, and text to columns. what is the
right way to do this or do I need a macro?