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

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

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

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…

: Microsoft Office Collaborate for free and online versions of Microsoft Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by Micr…

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…

Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…