Wednesday, October 14, 2009

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!




Gary said...

I run into this all the time, but Excel's string-manipulation is a pain in this regard. It is however tailor-made for regular expressions.

My approach is to cut the column in question, paste it into TextPad, and use a regular expression, in this case:
.* \([0-9]*\)ft.*$ - search & replace with the replacement expression \1 and you're done.

Since apparently you can add RegExes to VBA applications maybe a user-defined generic regex function is in order, so you can use the formula neatly?

Excel_Geek said...


You're dead on. I forgot to mention another constraint: no VBA. :(

I would have certainly taken a RegEx approach similar to what you're describing.

Thanks for the comment.

Gary said...

No VBA. You are a hero and deserve more than $50.