| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
delete middle word from a cell
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete middle word from a cell
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)))) "Wanna Learn" wrote: 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete middle word from a cell
Try Edit Replace with Find: ' * ' and Replace: ' '
[Explicitly: Find: single space followed by * followed by single space; Replace: single space] Wanna Learn wrote: 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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete middle word from a cell
Lori
PERFECT! thanks have agreat day "Lori" wrote: Try Edit Replace with Find: ' * ' and Replace: ' ' [Explicitly: Find: single space followed by * followed by single space; Replace: single space] Wanna Learn wrote: 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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete middle word from a cell
wow, that was brilliant!
:) "Lori" escreveu na mensagem ups.com... Try Edit Replace with Find: ' * ' and Replace: ' ' [Explicitly: Find: single space followed by * followed by single space; Replace: single space] Wanna Learn wrote: 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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete middle word from a cell
Thanks - you too..
Wanna Learn wrote: Lori PERFECT! thanks have agreat day "Lori" wrote: Try Edit Replace with Find: ' * ' and Replace: ' ' [Explicitly: Find: single space followed by * followed by single space; Replace: single space] Wanna Learn wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy text from word with returns into one cell | Excel Discussion (Misc queries) | |||
| copy excel cell to cell in word table using excel macro | Excel Discussion (Misc queries) | |||
| Delete all contents of cell but one word | Excel Discussion (Misc queries) | |||
| How can I use find and replace to delete a word in Excel? | Excel Discussion (Misc queries) | |||
| Possible Lookup Table | Excel Worksheet Functions | |||
Linear Mode
