Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Remove numbers from text cells
Hi all,
Does anyone know how to do this? Example of data... 71047 - INSPECT CHIMNEY 71334 INTERNAL ALTERATION AS RFQ3369 72107 - CLASSROOM EXTENSION 90082 WORKS TO MAIN PLAYGROUND I don't need the numbers and want the cells to just read INSPECT CHIMNEY INTERNAL ALTERATION AS RFQ3369 CLASSROOM EXTENSION WORKS TO MAIN PLAYGROUND Many thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove numbers from text cells
Hi Jay,
Am Wed, 22 Aug 2012 10:34:11 +0000 schrieb Jay07: 71047 - INSPECT CHIMNEY 71334 INTERNAL ALTERATION AS RFQ3369 72107 - CLASSROOM EXTENSION 90082 WORKS TO MAIN PLAYGROUND I don't need the numbers and want the cells to just read INSPECT CHIMNEY INTERNAL ALTERATION AS RFQ3369 CLASSROOM EXTENSION WORKS TO MAIN PLAYGROUND your text in A1 then: =TRIM(SUBSTITUTE(MID(A1,LEN(LOOKUP(9^9,LEFT(A1,COL UMN(1:1))*1))+1,99),"-",)) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove numbers from text cells
Hi Jay,
Am Wed, 22 Aug 2012 15:55:40 +0200 schrieb Claus Busch: your text in A1 then: =TRIM(SUBSTITUTE(MID(A1,LEN(LOOKUP(9^9,LEFT(A1,COL UMN(1:1))*1))+1,99),"-",)) better and shorter: =TRIM(SUBSTITUTE(MID(A1,FIND(" ",A1),99),"-",)) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove numbers from text cells
On Wednesday, August 22, 2012 3:34:11 AM UTC-7, Jay07 wrote:
Hi all, Does anyone know how to do this? Example of data... 71047 - INSPECT CHIMNEY 71334 INTERNAL ALTERATION AS RFQ3369 72107 - CLASSROOM EXTENSION 90082 WORKS TO MAIN PLAYGROUND I don't need the numbers and want the cells to just read INSPECT CHIMNEY INTERNAL ALTERATION AS RFQ3369 CLASSROOM EXTENSION WORKS TO MAIN PLAYGROUND Many thanks -- Jay07 I believe I got there using Find - (dash) and Replace All w/nothing. Then... =MID(G4,FIND(" ",G4+1,LEN(G4)-FIND(" ",G4))) Adjust range to suit, and you may want to do a COPY - PASTE SPECIAL - VALUES to get rid of the formulas. Hope yhat helps. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove and Add Text to Numbers | Excel Programming | |||
Easiest way to remove text from a cell that has text and numbers? | Excel Discussion (Misc queries) | |||
remove text from cell containing numbers | Excel Discussion (Misc queries) | |||
Remove Numbers from text | Excel Worksheet Functions | |||
Remove leading Numbers from text | Excel Worksheet Functions |