Solved

Formula to pick a number out of a text string in Excel

Posted on 2013-11-07
3
665 Views
Last Modified: 2014-07-01
I've been working on a formula to pick a number out of a text string.  Unlike all of the other solutions I've found on the web, I want to find a positive OR negative number, integer or floating point.  I've got it really close.  It basically picks the number with the largest absolute value out of the array consisting of every possible substring within the string.  The only problem is that if I have a string like 65yds-67.890g%4grj4 it will find the 890 as that is a valid number.  I would like it to return the -67.890 so what I really want is to find the longest string that is a valid number.

This is the first formula I had that works pretty well, it just has the aforementioned problem...

=IF(MAX(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),0)>ABS(MIN(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1))),MAX(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),0),MIN(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1)))

Open in new window


It's an array formula, so you have to enter it with Ctrl+Shft+Enter.

This formula finds the length of the longest string that is a valid number, I just haven't been able to pull out the string itself.

=LARGE(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),LEN(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1,""),1)

Open in new window


What's killing me is that functions like MATCH and VLOOKUP won't find values in a two-dimensional array.

I don't want to write a custom function in VBA.

Is there an expert out there that can help me finish my masterpiece?

For people who can't stand the code snippet box, here's the first formula in raw form...

=IF(MAX(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),0)>ABS(MIN(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1))),MAX(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),0),MIN(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1)))


And here's the second.

=LARGE(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),LEN(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1,""),1)
0
Comment
Question by:StudmillGuy
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 39632038
What about this version based on your latter formula

=LOOKUP(9.99E+307,MID(A2,LEN(A2)+1-ROW(INDIRECT("1:"&LEN(A2))),LARGE(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),LEN(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1,""),1))+0)

It uses the length returned by your formula and examines every substring of that length from your string, returning the last one that matches - from your example I get -67.89 because when you convert to a number it loses the trailing zero

If you want the actual 7 character string, i.e. -67.890 try this one

=MID(A2,1+LEN(A2)-MATCH(9.99E+307,MID(A2,LEN(A2)+1-ROW(INDIRECT("1:"&LEN(A2))),LARGE(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),LEN(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1,""),1))+0),LARGE(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),LEN(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1,""),1))

regards, barry
0
 

Author Closing Comment

by:StudmillGuy
ID: 39632048
That's exactly what I want.  I just couldn't figure out how to make one of the lookup functions work across a two-dimensional array.  Thanks.
0
 

Author Comment

by:StudmillGuy
ID: 40170984
I wanted to post an update that there is actually a bug in Barry's first formula.  A number with a zero in the decimal portion will produce an incorrect result - it ignores the rest of the number after the zero.  If the string in question were 65yds-67.89019g%4grj4, the first formula still only returns -67.89.  I have yet to find any problem with the second formula.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question