Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-11-07
3
Medium Priority
?
714 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

824 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