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

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

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

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