Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-11-07
3
Medium Priority
?
703 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 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

Technology Partners: 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

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.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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…

705 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