Extracting numbers from text

Howdy all

Ive got the following in a cell...

Scratched: 4-Bardon Fella, 6-Del's Boy, 10-Country View, 11-Tornado
Storm, 12-Tudor Inn.

And I need to extract the numbers

4
6
10
11
12

each into its own cell... what would be the best way of extracting
the numbers.....

One way, by brute force:

This is for 5 horses.
Need more, just add more "Finds".

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

=MID(A1,FIND("-",A1,FIND("-",A1)+1)-2,2)

=MID(A1,FIND("-",A1,FIND("-",A1,FIND("-",A1)+1)+1)-2,2)

=MID(A1,FIND("-",A1,FIND("-",A1,FIND("-",A1,FIND("-",A1)+1)+1)+1)-2,2)

=MID(A1,FIND("-",A1,FIND("-",A1,FIND("-",A1,FIND("-",A1,FIND("-",A1)+1)+1)+1
)+1)-2,2)

These will return text numbers, but I'm sure you're not going to calculate
with them.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"The Boondock Saint" <robport@(no-spam)inspire.net.nz> wrote in message
news:42a8bb28@clear.net.nz...
> Howdy all
>
> Ive got the following in a cell...
>
> Scratched: 4-Bardon Fella, 6-Del's Boy, 10-Country View, 11-Tornado
> Storm, 12-Tudor Inn.
>
> And I need to extract the numbers
>
> 4
> 6
> 10
> 11
> 12
>
> each into its own cell... what would be the best way of extracting
> the numbers.....
>
>
>

The Boondock Saint (no-spam) wrote...
>Ive got the following in a cell...
>
>Scratched: 4-Bardon Fella, 6-Del's Boy, 10-Country View, 11-Tornado
>Storm, 12-Tudor Inn.
>
>And I need to extract the numbers
>
>4
>6
>10
>11
>12
>
>each into its own cell... what would be the best way of extracting
>the numbers.....

Simplest would be a 2 cell per result approach. If your string were in
a cell named s, then enter the following formulas.

A3 [array formula]:
=MIN(FIND(ROW(INDIRECT("1:10"))-1,s&"0123456789"))

B3 [array formula]:
=MID(s,A3,MATCH(FALSE,ISNUMBER(-MID(s,A3,ROW(INDIRECT("1:16")))),0)-1)

A4 [array formula]:
=MIN(FIND(ROW(INDIRECT("1:10"))-1,s&"0123456789",A3+LEN(B3)))

Fill B3 down into B4, then select A4:B4 and fill down as far as needed.
The extracted numeric substrings will be in column B. The col B
formulas will return "" when the numeric substrings have been
exhausted.

If you want a 1 cell per result approach, the formulas get much longer
and much less efficient.

C3 [array formula]:
=MID(s,SMALL(IF(ISNUMBER(-MID(s&" ",seq,1))
-ISNUMBER(-MID(" "&s,seq,1))=1,seq),ROWS(C$3:C3)),
MATCH(FALSE,ISNUMBER(-MID(s,SMALL(IF(ISNUMBER(-MID(s&" ",seq,1))
-ISNUMBER(-MID(" "&s,seq,1))=1,seq),ROWS(C$3:C3)),seq)),0)-1)

Fill C3 down as far as needed. The col C formulas will return #NUM!
when the numeric substrings have been exhausted.

Much more flexible would be a udf using VBScript regular expressions,
such as the Subst function in

http://groups-beta.google.com/group/...74d1d78a685f59

(or http://makeashorterlink.com/?J17D21B3B ). Use it in formulas like

D3:
=subst(s,"^\D*(\d+\D+){"&(ROWS(D$3:D3)-1)&"}(\d+).+","$2")

Fill D3 down as far as needed. The col D formulas will return the
entire string s when the numeric substrings have been exhausted.

I would use Data > Text to columns to separate the elements into their own
columns, then use the "ASAP Utilities" feature to delete all the Alpha
characters, then Find&Replace any remaining undesirable characters with
"nothing"

"ASAP Utilities" is a free add-in at www.ASAP-utilities.com


Vaya con Dios,
Chuck, CABGx3


"The Boondock Saint" <robport@(no-spam)inspire.net.nz> wrote in message
news:42a8bb28@clear.net.nz...
> Howdy all
>
> Ive got the following in a cell...
>
> Scratched: 4-Bardon Fella, 6-Del's Boy, 10-Country View, 11-Tornado
> Storm, 12-Tudor Inn.
>
> And I need to extract the numbers
>
> 4
> 6
> 10
> 11
> 12
>
> each into its own cell... what would be the best way of extracting
> the numbers.....
>
>
>