Tricky Number Extraction from String

[Oswold]

Hello all,

I'm trying to compute the area (Length x Width) contained in a cell.

In a column, I have strings that contain length and width in the following way:

Please Login or Register  to view this content.

The cells contain: Feet.1/12inch x Feet.1/2inch

A single cell may contain multiple items, delimited with a "," (comma)

How can I parse this cell to calculate the area, even if there are multiple items in one cell?

The attached workbook explains the problem well.

Any suggestions greatly appreciated. If necessary, I can break separate items up into multiple columns. It's like this is to keep data entry as simple as possible, but amendments can be made. Thank you for your time.

Edit: Fixed workbook

[FlameRetired]

Hello Oswold. Welcome to the forum.

Your attachment flags as "Invalid". Sometimes these things happen here. I've notified Admin. In the meantime please try to upload another copy again.

[Oswold]

Thank you, Flame. Should be fixed now.

[Richard Buttrey]

Here's one UDF that will do it

Please Login or Register  to view this content.

[Oswold]

Richard, man that's great. Thank you! I'm going to work on turning your solution into a formula. I want to try to avoid VB now for the user's sake.

Marking as solved. Will post here later how it's done for posterity. I think I will have to break items up into separate columns if not using VB.

[Richard Buttrey]

Yes, it's perfecty doable with functions. You'll certainly need (or at least it will be preferable) to use helper columns if only for readability.

[Oswold]

Richard's solution inspired me to nearly work this out formulaically. Though I think his VB function is the way to go... Here's where I'm banging my head against a wall:

I'm having trouble parsing the decimals consistently. I've boiled it down for the need to do this:

Please Login or Register  to view this content.

[FlameRetired]

I've also been working on a formula approach most of this afternoon and am getting stuck on the decimals, too. I thought I had it licked with DOLLARDE, but the variable decimal places foils my efforts.

[Oswold]

Seems deceptively simple, doesn't it? It's bugging me now just out of personal satisfaction of problem solving it... That said, Richard's function is the best tool for the job. Doesn't make sense to avoid VB functions, so I've implemented it.

If a formulaic solution hits me, I will post it here for posterity.

Thank you both.

[FlameRetired]

Got a formula. It isn't pretty. The VBA approach wins.

I had to "double-parse" this.

Clear out @ 14 columns for this.

In D3 down and across to @ J6 this parses each operation at the commas.

Please Login or Register  to view this content.

Then in L3:R6 this parses the decimals and adds the fractions.

Please Login or Register  to view this content.

[Oswold]

Job well done! Bravo. Talking about pulling a rabbit out of the hat with Excel. Lol. Impressive work. Frankly, I'm blown away by your ability to parse with those functions like that.

My nested ifs still can't catch the corner cases like your crafted formulas. There's a lot to learn from this...

Thank you, Flame

[FlameRetired]

You are welcome. Glad to help and glad to hear you are eager to learn.

Thank you for the feedback, added rep and marking your thread Solved.