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...

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:"

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

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

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!

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,…

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…