delete middle word from a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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))))
 
Try Edit > Replace with Find: ' * ' and Replace: ' '
[Explicitly: Find: single space followed by * followed by single space;
Replace: single space]
 
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...
 
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...
 
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...
 
Back
Top