Extracting numbers from text

  • Thread starter Thread starter The Boondock Saint
  • Start date Start date
T

The Boondock Saint

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 (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.....
Click to expand...

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/microsoft.public.excel.worksheet.functions/msg/9d74d1d78a685f59

(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
 
Back
Top