delete middle word from a cell

W

Wanna Learn

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?
 
B

Barb Reinhardt

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

Lori

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

Wanna Learn

Lori

PERFECT! thanks have agreat day

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

Wanna said:
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?
Click to expand...
Click to expand...
 
R

Raffael

wow, that was brilliant!
:)


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

Wanna said:
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?
Click to expand...
Click to expand...
 
L

Lori

Thanks - you too..

Wanna said:
Lori

PERFECT! thanks have agreat day

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

Wanna said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Top