Remove variable text in cells
Hi,
I have a list of data that I want to edit and have tried LEFT,RIGHT & MIDDLE formulas for editing with no luck. Eg. 00123456 draft plan v.1 19-05-05 0012457 compliance plan v.2 26-06-05 012348 national business strategy v.2 07-06-05 I want to remove the date from each cell so it reads 00123456 draft plan v.1 0012457 compliance plan v.2 012348 national business strategy v.2 But because the cell characters vary and there isn't a common delimeter(-, _ .. ) I can't get excel to remove the date (last 8 digits) from the right. Any help would be greatly appreciated becuase it's driving me crazy |
If the data always ends in the date in this format then:
=LEFT(A1,LEN(A1)-9) Hope this helps Rowan "BHalberstater" wrote: Hi, I have a list of data that I want to edit and have tried LEFT,RIGHT & MIDDLE formulas for editing with no luck. Eg. 00123456 draft plan v.1 19-05-05 0012457 compliance plan v.2 26-06-05 012348 national business strategy v.2 07-06-05 I want to remove the date from each cell so it reads 00123456 draft plan v.1 0012457 compliance plan v.2 012348 national business strategy v.2 But because the cell characters vary and there isn't a common delimeter(-, _ . ) I can't get excel to remove the date (last 8 digits) from the right. Any help would be greatly appreciated becuase it's driving me crazy |
One way ..
Assuming data is in col A, A1 down Try in say, B1: =LEFT(TRIM(A1),SEARCH(" ",TRIM(A1),SEARCH(".",TRIM(A1)))) Copy B1 down -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "BHalberstater" wrote in message ... Hi, I have a list of data that I want to edit and have tried LEFT,RIGHT & MIDDLE formulas for editing with no luck. Eg. 00123456 draft plan v.1 19-05-05 0012457 compliance plan v.2 26-06-05 012348 national business strategy v.2 07-06-05 I want to remove the date from each cell so it reads 00123456 draft plan v.1 0012457 compliance plan v.2 012348 national business strategy v.2 But because the cell characters vary and there isn't a common delimeter(-, _ . ) I can't get excel to remove the date (last 8 digits) from the right. Any help would be greatly appreciated becuase it's driving me crazy |
Max wrote...
One way .. Assuming data is in col A, A1 down Try in say, B1: =LEFT(TRIM(A1),SEARCH(" ",TRIM(A1),SEARCH(".",TRIM(A1)))) .... Could fail when there's more than one period in the text. If the ending substring could be variable length but always preceded by a space, it's possible to use =LEFT(TRIM(A1),LOOKUP(2,1/(MID(TRIM(A1),ROW(INDIRECT("1:256")),1)=" "), ROW(INDIRECT("1:256")))-1) This relies on the functionality of the LOOKUP formula as it's worked from Excel 97 through Excel 2003 (and probably in earlier versions as well). |
All times are GMT +1. The time now is 06:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com