Extracting numbers (feet and inches) from cell

[jhblack76]

I'm trying to extract multiple feet and inches values from a single cell. Have used several iterations of SUBSTITUTE with VALUE((MID(FIND))), but haven't found the right combination for variable length numbers.

In B9: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,CHAR(39),"[ftwide]",1),CHAR(34),"[inwide]",1),CHAR(120),"[by]",1),CHAR(34),"[indeep]",1),CHAR(39),"[ftdeep]",1),CHAR(45),CHAR(32))
In C9: =MID(B9,FIND("[ftwide]",B9,9)-7,7)
In D9: =MID(B9,FIND("[inwide]",B9,9)-7,7)
In E9: =MID(B9,FIND("[ftdeep]",B9,9)-7,7)
In F9: =MID(B9,FIND("[indeep]",B9,9)-7,7)

Any help is appreciated!

DESIRED OUTPUT
String	Substitute	Feet Wide	Inches Wide	Feet Deep	Inches Deep
PGB-1 - 123'-1" x 100.375'-11.625"	PGB 1 123[ftwide] 1[inwide] [by] 100.375[ftdeep] 11.625[indeep]	123.000	1.000	100.375	11.625
PGB-4 - 18.50" x 84"	PGB 4 18.50[inwide] [by] 84[indeep]	-	18.500	-	84.000
PGB-9 - 6'-5" x 8'-10"	PGB 9 6[ftwide] 5[inwide] [by] 8[ftdeep] 10[indeep]	6.000	5.000	8.000	10.000
RESULTS
String	Substitute	Feet Wide	Inches Wide	Feet Deep	Inches Deep
PGB-1 - 123'-1" x 100.375'-11.625"	PGB 1 123[ftwide] 1[inwide] [by] 100.375[ftdeep] 11.625[indeep]	",B9,9)-7,7)] 1 123	wide] 1	",B9,9)-7,7)]100.375	",B9,9)-7,7)]11.625
PGB-4 - 18.50" x 84"	PGB 4 18.50[inwide] [by] 84[indeep]	#VALUE!	",B10,9)-7,7)]18.50	#VALUE!	[by] 84
PGB-9 - 6'-5" x 8'-10"	PGB 9 6[ftwide] 5[inwide] [by] 8[ftdeep] 10[indeep]	B 9 6	wide] 5	[by] 8	eep] 10

 

[jasonb75]

Looking at the table in your last reply, I'm not actually seeing what you're struggling with, you appear to have all of the results needed derived from formulas.

A couple of the decimal portions look a bit inaccurate but that's probably where you've tried combining them in the same cell, that is always going to cause problems.