Remove Text

  • Thread starter Thread starter Dee
  • Start date Start date
D

Dee

I have a worksheet in which one of the columns contains
text and numbers. For example, in each cell in the column
I would have "Patient RT 09-007 Assessment" What I need to
do is remove the text from the cells so that all that
remains is 09-007. Is there a function or some other way
of accomplishing this.

Thanks very much for any help.

Best regards,

Dee
 
Assuming the information you want to edit is consistently arranged, use:

=MID(A1,FIND("-",A1)-2,6)

This will find the hyphen, go 2 characters back, and return the next 5
characters from that point.

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh
 
Hi
you may provide some more information about the structure
of your data. If your data always starts with some text
and you want all the remaining information after the first
number you may try the following array formula (entered
with CTRL+SHIFT+ENTER):

=MID(A1,MIN(IF(ISNUMBER(--MID(A1,ROW(INDIRECT
("1:1024")),1)),ROW(INDIRECT("1:1024")))),255)
 
Frank Kabel said:
you may provide some more information about the structure
of your data. If your data always starts with some text
and you want all the remaining information after the first
number you may try the following array formula (entered
with CTRL+SHIFT+ENTER):

=MID(A1,MIN(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:1024")),
1)),ROW(INDIRECT("1:1024")))),255)
Click to expand...
....

First a quibble: you only need a single - inside ISNUMBER.

However, the formula could be reduced to

=MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1&"0",
ROW(INDIRECT("1:1024")),1)),0),1024)

As an added bonus, the latter won't return an error when there are no
decimal numerals in A1.
 
[...]
=MID(A1,MIN(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:1024")),
....

First a quibble: you only need a single - inside ISNUMBER. Ack.

However, the formula could be reduced to

=MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1&"0",
ROW(INDIRECT("1:1024")),1)),0),1024)
Click to expand...

nice one, better than mine!. Though the added benefit of
not returning an error if no number exist could also be
achieved using your suggestion with
=MID(A1,MIN(IF(ISNUMBER(--MID(A1&"0",ROW(INDIRECT
("1:1024")),1)),ROW(INDIRECT("1:1024")))),255)

Frank
 
Back
Top