Extracting Numbers from a huge list of alphabets

[Komalkkk]

Hi there,

In each cell of my excel sheet, I have a huge list of alphabets and buried in there is a number. It can be really anywhere in the text, with neither a fixed position nor length. I spent an entire afternoon trying to figure out how to extract it, the closest I got was extracting a number (used a formula from a previous thread at this forum), but without any decimal point consideration. (Most of these numbers are not integers). Attached here is an example. Please help!

Thank you! Your time is appreciated.

 

[bosco_yip]

Try……

=LOOKUP(1E+307,--MID(SUBSTITUTE(A1,".",),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/17)),ROW($1:$15)))

Regards

 

[shrivallabha]

Try……

=LOOKUP(1E+307,--MID(SUBSTITUTE(A1,".",),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/17)),ROW($1:$15)))

Regards

I think OP wants it with decimal so SUBSTITUTE portion could be dropped.
=LOOKUP(1E+307,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/17)),ROW($1:$15)))
Where did you find 1/17 ?

 

[bosco_yip]

.........
Where did you find 1/17 ?

Hi,

Here've some more :

3^45
5^19
7^18
7^56
12^15

Regards
Bosco

 

[Khalid NGO]

Hi,

Can be extract easily IF there is a pattern:

FGABMNTYYYYYAGGG[1234.50]GGGYTRMMMMAVVVMY

Regards,

 

[Komalkkk]

I think OP wants it with decimal so SUBSTITUTE portion could be dropped.
=LOOKUP(1E+307,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/17)),ROW($1:$15)))
Where did you find 1/17 ?

Thank you! This worked like a charm. Though, after about 7 rows, it starts throwing N/A. Any suggestions. Thanks again.

 

[Komalkkk]

Hi,

Can be extract easily IF there is a pattern:

FGABMNTYYYYYAGGG[1234.50]GGGYTRMMMMAVVVMY

Regards,

Yes, there is a pattern. These numbers are always in square brackets.

 

[Komalkkk]

I think OP wants it with decimal so SUBSTITUTE portion could be dropped.
=LOOKUP(1E+307,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/17)),ROW($1:$15)))
Where did you find 1/17 ?

Never mind, got that taken care of, but realized there are some numbers which are preceded by a negative sign e.g. AAAA[-33.30]GYY. Any suggestion on how can I have the output with negative included? Thank you!

 

[Komalkkk]

Try……

=LOOKUP(1E+307,--MID(SUBSTITUTE(A1,".",),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/17)),ROW($1:$15)))

Regards

Thank you for your reply! It did output a number, but excluded decimal.

 

[Khalid NGO]

Yes, there is a pattern. These numbers are always in square brackets.

Try:

=--MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1)

Regards,