Left, Find, Substitute suggestion

[rex759]

Hello,
The goal is show the information between certain numbers in Column B from Column A. Column C is the desired results.

The description always starts at the 13th space from the beginning and ends with the last set of numbers containing a decimal point.

In this example, the result would be 1.38INX1.38INX35IN RWD SQ END BAL.

Example:
0000-123-456 1.38INX1.38INX35IN RWD SQ END BAL 4.98 810 471 339- 0000-105-037 _______ 1804 345 _______

The formula I am using woks fine as long as there isn’t other decimal points in the description. If more than one decimal point, I only get a partial result. I though I can do a global change (find/replace) the decimal points with a space, but I need the decimal point for the last set of numbers for something else.
Any ideas or suggestions is appreciated.

Description Ex.xlsx
	A	B	C
2	0000-123-456 1.38INX1.38INX35IN RWD SQ END BAL 4.98 810 471 339- 0000-105-037 _______ 1804 345 _______	1	1 38INX1.38INX35IN RWD SQ END BAL
3	0000-123-456 0.354IN X 48IN X 96IN; 3/8 CAT 29.07 146 92 54- 0000-166-065 _______ 3129 92 _______	0.354IN X 48IN X 96IN; 3/8 CAT	0.354IN X 48IN X 96IN; 3/8 CAT
4	0000-123-456 0.563IN X 48IN X 96IN; 19/32 CAT 44.01 50 118 68 0000-166-081 _______ 3131 58 _______	0.563IN X 48IN X 96IN; 19/32 CAT	0.563IN X 48IN X 96IN; 19/32 CAT
5	0000-123-456 0.703IN X 48IN X 96IN; 23/32 CAT 52.89 60 92 32 0000-166-103 _______ 3131 48 _______	0.703IN X 48IN X 96IN; 23/32 CAT	0.703IN X 48IN X 96IN; 23/32 CAT
6	0000-123-456 0.625INX3.5INX72IN INCENSE CDR DE PK 2.74 1890 2950 1060 0000-166-227 _______ 3806 540 _______	0	0 625INX3.5INX72IN INCENSE CDR DE PK
7	0000-123-456 0.29IN X 8.18IN X 143.8IN CDR HARDIE 14.32 587 970 383 0000-230-196 _______ 3129 50 _______	0.29IN X	0 29IN X 8 18IN X 143.8IN CDR HARDIE
8	0000-123-456 0.578IN X 47.75INX95.75IN; OSB 32.30 233 162 71- 0000-339-696 _______ 3131 96 _______	0.578IN X	0 578IN X 47 75INX95.75IN; OSB
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=IFERROR(TRIM(MID(SUBSTITUTE(LEFT(A2,FIND(".",A2,IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=1,1,FIND(".",A2)+1)))," ",REPT(" ",100),LEN(LEFT(A2,FIND(".",A2,IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=1,1,FIND(".",A2)+1))))-LEN(SUBSTITUTE(LEFT(A2,FIND(".",A2,IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=1,1,FIND(".",A2)+1)))," ",""))),FIND(" ",A2)+1,100)),"")

 

[JGordon11]

Book2
	A	B	C
1	0000-123-456 1.38INX1.38INX35IN RWD SQ END BAL 4.98 810 471 339- 0000-105-037 _______ 1804 345 _______	1.38INX1.38INX35IN RWD SQ END BAL
2	0000-123-456 0.354IN X 48IN X 96IN; 3/8 CAT 29.07 146 92 54- 0000-166-065 _______ 3129 92 _______	0.354IN X 48IN X 96IN; 3/8 CAT
3	0000-123-456 0.563IN X 48IN X 96IN; 19/32 CAT 44.01 50 118 68 0000-166-081 _______ 3131 58 _______	0.563IN X 48IN X 96IN; 19/32 CAT
4	0000-123-456 0.703IN X 48IN X 96IN; 23/32 CAT 52.89 60 92 32 0000-166-103 _______ 3131 48 _______	0.703IN X 48IN X 96IN; 23/32 CAT
5	0000-123-456 0.625INX3.5INX72IN INCENSE CDR DE PK 2.74 1890 2950 1060 0000-166-227 _______ 3806 540 _______	0.625INX3.5INX72IN INCENSE CDR DE PK
6	0000-123-456 0.29IN X 8.18IN X 143.8IN CDR HARDIE 14.32 587 970 383 0000-230-196 _______ 3129 50 _______	0.29IN X 8.18IN X 143.8IN CDR HARDIE
7	0000-123-456 0.578IN X 47.75INX95.75IN; OSB 32.30 233 162 71- 0000-339-696 _______ 3131 96 _______	0.578IN X 47.75INX95.75IN; OSB
8
Sheet2

Cell Formulas
Range	Formula
B1:B7	B1	=LET(t, A1,l, LEN(t),r, TEXTJOIN("",1,MID(t,SEQUENCE(l,1,l,-1),1)), s, FIND(" ",r,FIND(".",r)), MID(t,14, l - s -13))

 

[rex759]

Textjoin? something new to me. Ok, will give it a try in the morning and let you know. Thank you

 

[rex759]

This works perfectly! I have't used those functions together and will have to dissect it to fully understand it. Thanks again

 

[Peter_SSs]

Textjoin? something new to me.

There are some other new functions gradually being rolled out. If you have them, or when you get them, it could be considerably simpler.
This one uses TEXTBEFORE.

22 08 10.xlsm
	A	B
1	0000-123-456 1.38INX1.38INX35IN RWD SQ END BAL 4.98 810 471 339- 0000-105-037 _______ 1804 345 _______	1.38INX1.38INX35IN RWD SQ END BAL
2	0000-123-456 0.354IN X 48IN X 96IN; 3/8 CAT 29.07 146 92 54- 0000-166-065 _______ 3129 92 _______	0.354IN X 48IN X 96IN; 3/8 CAT
3	0000-123-456 0.563IN X 48IN X 96IN; 19/32 CAT 44.01 50 118 68 0000-166-081 _______ 3131 58 _______	0.563IN X 48IN X 96IN; 19/32 CAT
4	0000-123-456 0.703IN X 48IN X 96IN; 23/32 CAT 52.89 60 92 32 0000-166-103 _______ 3131 48 _______	0.703IN X 48IN X 96IN; 23/32 CAT
5	0000-123-456 0.625INX3.5INX72IN INCENSE CDR DE PK 2.74 1890 2950 1060 0000-166-227 _______ 3806 540 _______	0.625INX3.5INX72IN INCENSE CDR DE PK
6	0000-123-456 0.29IN X 8.18IN X 143.8IN CDR HARDIE 14.32 587 970 383 0000-230-196 _______ 3129 50 _______	0.29IN X 8.18IN X 143.8IN CDR HARDIE
7	0000-123-456 0.578IN X 47.75INX95.75IN; OSB 32.30 233 162 71- 0000-339-696 _______ 3131 96 _______	0.578IN X 47.75INX95.75IN; OSB
Sheet2

Cell Formulas
Range	Formula
B1:B7	B1	=REPLACE(TEXTBEFORE(TEXTBEFORE(A1,".",-1)," ",-1),1,13,"")

 

[rex759]

I have many long formulas that could probably be shorten and made more efficient. This could really help. Thank you for the tip.