Left, Find, Substitute suggestion

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
587
Office Version
  1. 365
Platform
  1. Windows
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
ABC
20000-123-456 1.38INX1.38INX35IN RWD SQ END BAL 4.98 810 471 339- 0000-105-037 _______ 1804 345 _______11 38INX1.38INX35IN RWD SQ END BAL
30000-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 CAT0.354IN X 48IN X 96IN; 3/8 CAT
40000-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 CAT0.563IN X 48IN X 96IN; 19/32 CAT
50000-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 CAT0.703IN X 48IN X 96IN; 23/32 CAT
60000-123-456 0.625INX3.5INX72IN INCENSE CDR DE PK 2.74 1890 2950 1060 0000-166-227 _______ 3806 540 _______00 625INX3.5INX72IN INCENSE CDR DE PK
70000-123-456 0.29IN X 8.18IN X 143.8IN CDR HARDIE 14.32 587 970 383 0000-230-196 _______ 3129 50 _______0.29IN X0 29IN X 8 18IN X 143.8IN CDR HARDIE
80000-123-456 0.578IN X 47.75INX95.75IN; OSB 32.30 233 162 71- 0000-339-696 _______ 3131 96 _______0.578IN X0 578IN X 47 75INX95.75IN; OSB
Sheet1
Cell Formulas
RangeFormula
B2:B8B2=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)),"")
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

JGordon11

Well-known Member
Joined
Jan 18, 2021
Messages
554
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Book2
ABC
10000-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
20000-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
30000-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
40000-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
50000-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
60000-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
70000-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
RangeFormula
B1:B7B1=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))
 
Solution

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
587
Office Version
  1. 365
Platform
  1. Windows
Textjoin? something new to me. Ok, will give it a try in the morning and let you know. Thank you
 

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
587
Office Version
  1. 365
Platform
  1. Windows
This works perfectly! I have't used those functions together and will have to dissect it to fully understand it. Thanks again
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,563
Office Version
  1. 365
Platform
  1. Windows
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
AB
10000-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
20000-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
30000-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
40000-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
50000-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
60000-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
70000-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
RangeFormula
B1:B7B1=REPLACE(TEXTBEFORE(TEXTBEFORE(A1,".",-1)," ",-1),1,13,"")
 

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
587
Office Version
  1. 365
Platform
  1. Windows
I have many long formulas that could probably be shorten and made more efficient. This could really help. Thank you for the tip.
 

Similar threads

Forum statistics

Threads
1,176,042
Messages
5,901,092
Members
434,870
Latest member
michud08

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top