Extract Only Numbers From Text String

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
With
A1: 91.28ABC37.1D2F465
B1: 2

This variation of the array formula I posted returns embedded numbers
that can contain decimal values:
Code:
C1: =LOOKUP(10^99,--MID("|"&A1,SMALL(IF(((--ISNUMBER(--("0"&MID("|"&A1,
ROW($1:$25),1)))=0)*ISNUMBER(--(MID("|"&A1,ROW($2:$26),1)))),ROW($2:$26)),B1),
ROW($1:$25)))
In the above example, the formula returns the second embedded number:
37.1
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

pto160

Active Member
Joined
Feb 1, 2009
Messages
330
Office Version
365
Platform
Windows
This is going to work fantastic with embedded numbers, for example inventory parts. I will definitely use it for that.
The first array formula works with non-embedded numbers.
If the first number is 100.59, and you put in 1 for the position, it picks up 100.59, but if you put in 2, it picks up 59. The way around this is to go up by increments of two. But some numbers will be 464 and not 464.00, so the 3rd number will pick up the two digits after the decimal point.

Sheet3

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 235px;"> <col style="width: 254px;"> <col style="width: 88px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td> </td> <td>Formula</td> <td>Number of n</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>464.59 DDFSDF 23.25 ccd 157.25</td> <td style="text-align: right;">464.59</td> <td style="text-align: right;">1</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td>464.68 DDFSDF 23.25 ccd 157.25</td> <td style="text-align: right;">68.00</td> <td style="text-align: right;">2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>464 DDFSDF 23.25 ccd 157.25</td> <td style="text-align: right;">23.25</td> <td style="text-align: right;">2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td>464 DDFSDF 23.84 ccd 157.25</td> <td style="text-align: right;">84.00</td> <td style="text-align: right;">3</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"><tbody><tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B4</td> <td>{=LOOKUP(10^99,--MID("|"&A4,SMALL(IF(((--ISNUMBER(--MID("|"&A4,
ROW($1:$1003),1)
)
=0)
*ISNUMBER(--MID("|"&A4,ROW($2:$1004),1)))
,ROW($2:$1004))
,C4)
,
ROW($1:$1003))
)}</td></tr></tbody></table></td></tr> <tr> <td>Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!
</td></tr></tbody></table>
Maybe use another column to change numbers like 464 to 464.00 and then
use the formula on the revised column?


Excel tables to the web >>
Excel Jeanie HTML 4
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
I think we finally have an all-purpose formula.

With A1:A7 containing
Code:
45t*&65/
9128A+BC37/E*465
91a28ABC3712DEF465
91.28ABC3712DEF465
91.28ABC37.1D2F465
464.59 DDFSDF 23.25 ccd 157.25
123asdf.asdf.asdf456
 
and
C1: 2
This array formula returns the specified number from the string,
Code:
B1: =LOOKUP(10^99,--MID("|"&A1,SMALL(IF(((--ISNUMBER(--("0"&MID(
SUBSTITUTE(" "&A1," ","|"),ROW($1:$25),1)))=0)*ISNUMBER(--(MID(
SUBSTITUTE(" "&A1," ","|"),ROW($2:$26),1)))),ROW($2:$26)),C1),ROW($1:$25)))
Copy B1 and paste into B2:B7

With the above examples, the formulas return these values:
Code:
45
37
28
91.28
2
23.25
456
Note: If you want to display two decimal places, change the number format.

Are we done, yet?
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
Yikes! I posted the wrong returned value list!
Code:
[B]45[/B] <-----Should be 65
37
28
[B]91.28[/B] <--Should be 3712
[B]2[/B] <------Should be 37.1
23.25 
456
so the list of second embedded numbers should be this:
Code:
65
37
28
3712
37.1
23.25
 

pto160

Active Member
Joined
Feb 1, 2009
Messages
330
Office Version
365
Platform
Windows
Yes, we are definitely done. This formula can pretty much work with any type of text format that is thrown at it. I thought regex was the only option for this, but I was proved wrong. Thanks so much for your help.:)
 

jianniskouts

New Member
Joined
Mar 17, 2012
Messages
2
i just did what you said and pressing F9 i get the correct result but when i press control+shift+enter i get something that looks like 3,0697E+11 INSTEAD Of 306970490622...please help!!
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Just format the cell as Number and make the column wider.
 

Tcurious

New Member
Joined
Jan 22, 2013
Messages
1
Extract only numbers but need decimals

Schielrn,
The formula from Domenic at post 6:
Extracting Multiple Numbers from String
works great (I changed my string to cell A2):

=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)

Ron Coderre,
This formula is beautiful (I changed my string to cell A2):

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

Ron, I like your formula because of its compactness and non-array non-Ctrl-Shift-Enter.
I like Domenic’s because of the robustness of the ROW(INDIRECT("1:"&LEN(A2))).
Mashing your two formulas together (in Excel 2007 only – exceeds 7 nesting rule in Excel 2003) I got:

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN($A$2))),1))*
ROW(INDIRECT("1:"&LEN($A$2))),0),ROW(INDIRECT("1:"&LEN($A$2))))+1,1)*10^ROW(INDIRECT("1:"&LEN($A$2)))/10)

I learn a lot from both formulas. In essence, are these the concepts of how the formula is working:

1) Extract characters with MID
2) Convert extracted characters to numbers and errors with --
3) Ask array if elements are numbers with ISNUMBER
4) Multiply array of TRUE/FALSE by position -- *ROW(INDIRECT("1:"&LEN($A$2)))
5) Organize by position with LARGE (largest to smallest including zeroes)
6) Add 1 to avoid error with MID caused by zeros from LARGE
7) Concatenate a zero to beginning of string so the 1 added to the zeros will not cause error when MID extracts numbers
8) Multiply the extracted numbers by 10^ROW(INDIRECT("1:"&LEN($A$2))) to get the correct number of zeros for each extracted number
9) Divide by 10 to deal with the fact the we had a zero concatenated to the front of the string
10) Added

Am I getting the concepts, right?

I have found the above really useful and got it to work however have one big challenge I can't quite get my head around, what if the number is a decimal? Eg If the string is 2.28xbw I need it to extract 2.28 instead of 228 as these do, anyone any ideas?
 

williamezell

New Member
Joined
May 15, 2013
Messages
1
I played around with the formula some more...
This non-array formula seems to be working:
Code:
B1: =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)
That worked for me! Thanks!!! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,754
Messages
5,488,648
Members
407,650
Latest member
polonic

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top