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]

Is the format for all of your data consistent with the example? XXX-# - Feet / Inches x Feet / Inches ?

Using the second row as an example, should it remain as 18.5 Inches or be converted to 1 Foot 6.5 Inches?

 

[jasonb75]

Fiddling around a little with your example, this appears to work. It may be possible to refine some of the formulas slightly, this was the first set that worked.

Book1
	A	B	C	D	E
8	RESULTS
9	String	Feet Wide	Inches Wide	Feet Deep	Inches Deep
10	PGB-1 - 123'-1" x 100.375'-11.625"	123	1	100.375	11.625
11	PGB-4 - 18.50" x 84"	0	18.5	0	84
12	PGB-9 - 6'-5" x 8'-10"	6	5	8	10
Sheet3

Cell Formulas
Range	Formula
B10:B12	B10	=LET(s,SEARCH("|",SUBSTITUTE(A10," - "," | ",1))+1,e,SEARCH("|",SUBSTITUTE(A10,"'","|",1)),IFERROR(--TRIM(MID(A10,s+1,e-s-1)),0))
C10:C12	C10	=LET(s,IFERROR(SEARCH("'",A10),SEARCH("|",SUBSTITUTE(A10," - "," | ",1)))+1,e,SEARCH("|",SUBSTITUTE(A10,"""","|",1)),IFERROR(--TRIM(MID(A10,s+1,e-s-1)),0))
D10:D12	D10	=LET(s,SEARCH("|",SUBSTITUTE(A10," x "," | ",1))+1,e,SEARCH("|",SUBSTITUTE(A10,"'","|",2)),IFERROR(--TRIM(MID(A10,s+1,e-s-1)),0))
E10:E12	E10	=LET(s,IFERROR(SEARCH("|",SUBSTITUTE(A10,"'","|",2)),SEARCH("|",SUBSTITUTE(A10," x "," | ",1)))+1,e,SEARCH("|",SUBSTITUTE(A10,"""","|",2)),IFERROR(--TRIM(MID(A10,s+1,e-s-1)),0))

 

[jhblack76]

Is the format for all of your data consistent with the example? XXX-# - Feet / Inches x Feet / Inches ?

Using the second row as an example, should it remain as 18.5 Inches or be converted to 1 Foot 6.5 Inches?

The beginning of the string (XXX-#) is not consistent. The remainder (feet'-inches" x feet'-inches") will be consistent. I have other cells that will convert inches to feet, etc, so not concerned about that part. Data extraction is main concern.

 

[jhblack76]

Fiddling around a little with your example, this appears to work. It may be possible to refine some of the formulas slightly, this was the first set that worked.

Book1
	A	B	C	D	E
8	RESULTS
9	String	Feet Wide	Inches Wide	Feet Deep	Inches Deep
10	PGB-1 - 123'-1" x 100.375'-11.625"	123	1	100.375	11.625
11	PGB-4 - 18.50" x 84"	0	18.5	0	84
12	PGB-9 - 6'-5" x 8'-10"	6	5	8	10
Sheet3

Cell Formulas
Range	Formula
B10:B12	B10	=LET(s,SEARCH("|",SUBSTITUTE(A10," - "," | ",1))+1,e,SEARCH("|",SUBSTITUTE(A10,"'","|",1)),IFERROR(--TRIM(MID(A10,s+1,e-s-1)),0))
C10:C12	C10	=LET(s,IFERROR(SEARCH("'",A10),SEARCH("|",SUBSTITUTE(A10," - "," | ",1)))+1,e,SEARCH("|",SUBSTITUTE(A10,"""","|",1)),IFERROR(--TRIM(MID(A10,s+1,e-s-1)),0))
D10:D12	D10	=LET(s,SEARCH("|",SUBSTITUTE(A10," x "," | ",1))+1,e,SEARCH("|",SUBSTITUTE(A10,"'","|",2)),IFERROR(--TRIM(MID(A10,s+1,e-s-1)),0))
E10:E12	E10	=LET(s,IFERROR(SEARCH("|",SUBSTITUTE(A10,"'","|",2)),SEARCH("|",SUBSTITUTE(A10," x "," | ",1)))+1,e,SEARCH("|",SUBSTITUTE(A10,"""","|",2)),IFERROR(--TRIM(MID(A10,s+1,e-s-1)),0))

This is fantastic! There will be some cells that have / in them, such as:
PB - S3.04/14 - 12" x 54"

Anything I can add to the formula to strip / out of it so it will read those correctly?

 

[jasonb75]

This should fix it, note that the formula in C10 should have a double space between the " " of the second substitute function, sometimes the forum software trims them down to single spaces.

Book1
	A	B	C	D	E
9	String	Feet Wide	Inches Wide	Feet Deep	Inches Deep
10	PGB-1 - 123'-1" x 100.375'-11.625"	123	1	100.375	11.625
11	PGB-4 - 18.50" x 84"	0	18.5	0	84
12	PGB-9 - 6'-5" x 8'-10"	6	5	8	10
13	PB - S3.04/14 - 12" x 54"	0	12	0	54
Sheet3

Cell Formulas
Range	Formula
B10:B13	B10	=LET(s,SEARCH("|",SUBSTITUTE(A10," - "," | ",1))+1,e,SEARCH("|",SUBSTITUTE(A10,"'","|",1)),IFERROR(--TRIM(MID(A10,s+1,e-s-1)),0))
C10:C13	C10	=LET(s,IFERROR(SEARCH("'",A10),SEARCH("|",SUBSTITUTE(A10," - "," | ",LEN(A10)-LEN(SUBSTITUTE(A10," - "," ")))))+1,e,SEARCH("|",SUBSTITUTE(A10,"""","|",1)),IFERROR(--TRIM(MID(A10,s+1,e-s-1)),0))
D10:D13	D10	=LET(s,SEARCH("|",SUBSTITUTE(A10," x "," | ",1))+1,e,SEARCH("|",SUBSTITUTE(A10,"'","|",2)),IFERROR(--TRIM(MID(A10,s+1,e-s-1)),0))
E10:E13	E10	=LET(s,IFERROR(SEARCH("|",SUBSTITUTE(A10,"'","|",2)),SEARCH("|",SUBSTITUTE(A10," x "," | ",1)))+1,e,SEARCH("|",SUBSTITUTE(A10,"""","|",2)),IFERROR(--TRIM(MID(A10,s+1,e-s-1)),0))

 

[jhblack76]

Brilliant solution. I've spent the entire day on this and you solved it in minutes! Thank you!

 

[jasonb75]

Believe it or not this is actually quite a common type of question, although it is usually extracting names or parts of addresses rather than measurements but the basic principles are the same.

Some of the formula may still need a little fine tuning, I haven't tested the string in row 13 with feet and inches, only inches so that may need to be allowed for. Also, I haven't tested with one dimension being in feet and inches with the other in inches only, something I image could be possible if one dimension is less than 12 inches.

I'll keep your thread on my watch list in case you find any problems but fingers crossed all works ok.

 

[jhblack76]

I've tested multiple variations and it is working great, even managed to add a third dimension where needed. Thanks again. Have never used LET() before and struggling to understand the syntax. Struggling with another part. Trying to figure out how to adapt your work into another portion that looks like this (I left my old formula attempt showing so you can see the problem I was having).

Mark	Qty	Dia.	Pen	Top	Substitute	Dia_F	Pen_F	Top_F
A - 18" / 10'-0" / 91'-8.75"	2	18	10.00	97.25	A 18 [india] / / 10 [ftpen] 0 [inpen] / 91 [fttop] 8.75 [intop]	",F2)-2,2),0)]18	",F2)-2,2)+((MID(F2,FIND(" [inpen] ",F2)-2,2))/12),0)]10.00	",F2)-2,2)+((MID(F2,FIND(" [intop] ",F2)-2,2))/12),0)]97.25
A - 18" / 10'-0" / 92'-0"	7	18	10.00	92.00	A 18 [india] / / 10 [ftpen] 0 [inpen] / 92 [fttop] 0 [intop]	",F3)-2,2),0)]18	",F3)-2,2)+((MID(F3,FIND(" [inpen] ",F3)-2,2))/12),0)]10.00	",F3)-2,2)+((MID(F3,FIND(" [intop] ",F3)-2,2))/12),0)]92.00
A - 18" / 10'-0" / 96'-10"	28	18	10.00	96.83	A 18 [india] / / 10 [ftpen] 0 [inpen] / 96 [fttop] 10 [intop]	",F4)-2,2),0)]18	",F4)-2,2)+((MID(F4,FIND(" [inpen] ",F4)-2,2))/12),0)]10.00	",F4)-2,2)+((MID(F4,FIND(" [intop] ",F4)-2,2))/12),0)]96.83
24/60 - 24" / 60'-0" / 88'-0"	8	24	60.00	88.00	24/60 24 [india] / / 60 [ftpen] 0 [inpen] / 88 [fttop] 0 [intop]	",F5)-2,2),0)]24	",F5)-2,2)+((MID(F5,FIND(" [inpen] ",F5)-2,2))/12),0)]60.00	",F5)-2,2)+((MID(F5,FIND(" [intop] ",F5)-2,2))/12),0)]88.00
24/60 - 24" / 60'-0" / 90'-11.5"	14	24	60.00	90.04	24/60 24 [india] / / 60 [ftpen] 0 [inpen] / 90 [fttop] 11.5 [intop]	",F6)-2,2),0)]24	",F6)-2,2)+((MID(F6,FIND(" [inpen] ",F6)-2,2))/12),0)]60.00	",F6)-2,2)+((MID(F6,FIND(" [intop] ",F6)-2,2))/12),0)]90.04
24/60 - 24" / 60'-0" / 90'-6"	7	24	60.00	90.50	24/60 24 [india] / / 60 [ftpen] 0 [inpen] / 90 [fttop] 6 [intop]	",F7)-2,2),0)]24	",F7)-2,2)+((MID(F7,FIND(" [inpen] ",F7)-2,2))/12),0)]60.00	",F7)-2,2)+((MID(F7,FIND(" [intop] ",F7)-2,2))/12),0)]90.50

The data is typically well-formatted, but varies project-to-project.

 

[jasonb75]

Have never used LET() before and struggling to understand the syntax.

It's actually quite simple once you figure it out. It's not necessary to use LET, and any formula written with it can be written without. Using it makes things much easier and makes the formula shorter when large sections are repeated.

Taking the first formula from earlier, s is simply a name given to the section in bold and e a name given to the section in italic
=LET(s,SEARCH("|",SUBSTITUTE(A10," - "," | ",1))+1,e,SEARCH("|",SUBSTITUTE(A10,"'","|",1)),IFERROR(--TRIM(MID(A10,s+1,e-s-1)),0))

This means in the last part, IFERROR(--TRIM(MID(A10,s+1,e-s-1)),0) which is the actual formula, you can use s and e to shorten it instead of repeating the whole section again.
Although e was only use once, so it was not strictly necessary to use a name for it, I find it can make it easier for fine tuning formulas like this.

I'll try and have a look at the new data set later today and see what I can do with it.