Pull numeric value from string

The other day I had an interesting request for help via my little MeeboMe chat window. James wanted a formula to pull a numeric value from a string contained in another cell. The string was something like this:

"Nylon rope 300Ft"

He had a whole column of this type of data and wanted to pull out the number of feet of each item. The "Ft" would always be immediately after the numeric value desired, and a space would always immediately precede it, but here's the kicker: there could be any number of other spaces before the numeric value and there could also be other number before the numeric value desired.

It took me a bit to get where I needed to be, but here's the logic built into a pretty long, nasty, nested formula:

1. First get the string that contains everything up to and including the "Ft" using LEFT and FIND.


2. Count the number of spaces in the string identified in #1 by comparing the length of that string versus that string if all the spaces are replaced with nothing (""), using LEN and SUBSTITUTE.


3. Replace the last space found in the string identified in #1 with an odd character not likely to be in any of the data (I used a tilde ~) using SUBSTITUTE.


4. Pull out the numeric value, which is the remainder of the string after the tilde using MID, FIND, and LEN.

I put together a sample spreadsheet of this process both broken out into parts and also all nested together in a single formula.

Excel_Geek Insiders, enjoy!

Later,

Excel_Geek