Remove Text

[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

 

[Michael Malinsky]

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

 

[Frank Kabel]

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)

 

[Harlan Grove]

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)

....

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.

 

[Frank Kabel]

[...]
=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)

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