| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find & and delete everything following
Hi There,
I have a spreadsheet with names in one column, however to load this file I need to format correctly. EG column contains: James Smith & Sarah Brown I need to delete everything from '&' onwards. (I have used text to columns using & as the delimiter, however there are instances of three or four names!) Is there are delete function I can use? Could you please help? Thanks in advance! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find & and delete everything following
Hi,
Try this =LEFT(EG3,SEARCH("&",EG3,1)-2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "fleura26" wrote in message ... Hi There, I have a spreadsheet with names in one column, however to load this file I need to format correctly. EG column contains: James Smith & Sarah Brown I need to delete everything from '&' onwards. (I have used text to columns using & as the delimiter, however there are instances of three or four names!) Is there are delete function I can use? Could you please help? Thanks in advance! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find & and delete everything following
YAYYYY, you just saved me hours of manual work. Thank you thank you thank
you!!!!!!! "Ashish Mathur" wrote: Hi, Try this =LEFT(EG3,SEARCH("&",EG3,1)-2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "fleura26" wrote in message ... Hi There, I have a spreadsheet with names in one column, however to load this file I need to format correctly. EG column contains: James Smith & Sarah Brown I need to delete everything from '&' onwards. (I have used text to columns using & as the delimiter, however there are instances of three or four names!) Is there are delete function I can use? Could you please help? Thanks in advance! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find & and delete everything following
You may want to search for " &" or add =trim() around that function.
Another possible way is to Select the column Edit|replace what: & (or spacebar ampersand) with: (leave blank) replace all This suffers the same problem if there are varying number of spaces in front of that ampersand. fleura26 wrote: YAYYYY, you just saved me hours of manual work. Thank you thank you thank you!!!!!!! "Ashish Mathur" wrote: Hi, Try this =LEFT(EG3,SEARCH("&",EG3,1)-2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "fleura26" wrote in message ... Hi There, I have a spreadsheet with names in one column, however to load this file I need to format correctly. EG column contains: James Smith & Sarah Brown I need to delete everything from '&' onwards. (I have used text to columns using & as the delimiter, however there are instances of three or four names!) Is there are delete function I can use? Could you please help? Thanks in advance! -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find & and delete everything following
You are welcome.
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "fleura26" wrote in message ... YAYYYY, you just saved me hours of manual work. Thank you thank you thank you!!!!!!! "Ashish Mathur" wrote: Hi, Try this =LEFT(EG3,SEARCH("&",EG3,1)-2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "fleura26" wrote in message ... Hi There, I have a spreadsheet with names in one column, however to load this file I need to format correctly. EG column contains: James Smith & Sarah Brown I need to delete everything from '&' onwards. (I have used text to columns using & as the delimiter, however there are instances of three or four names!) Is there are delete function I can use? Could you please help? Thanks in advance! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find & and delete everything following
Without using a formula, you can use find & replace, replacing &* with
nothing (that's ampersand & asterisk) Bob Umlas Excel MVP "Ashish Mathur" wrote in message ... You are welcome. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "fleura26" wrote in message ... YAYYYY, you just saved me hours of manual work. Thank you thank you thank you!!!!!!! "Ashish Mathur" wrote: Hi, Try this =LEFT(EG3,SEARCH("&",EG3,1)-2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "fleura26" wrote in message ... Hi There, I have a spreadsheet with names in one column, however to load this file I need to format correctly. EG column contains: James Smith & Sarah Brown I need to delete everything from '&' onwards. (I have used text to columns using & as the delimiter, however there are instances of three or four names!) Is there are delete function I can use? Could you please help? Thanks in advance! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find and delete | Excel Worksheet Functions | |||
| Find and delete! | Excel Discussion (Misc queries) | |||
| Find and Delete | Excel Worksheet Functions | |||
| find a value and delete that row | Excel Discussion (Misc queries) | |||
| Find delete | Excel Discussion (Misc queries) | |||
Linear Mode
