Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2013-11-07
3
Medium Priority
?
739 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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

601 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