Finding numbers in cell with text

[Lancer7]

Cell B2 Specs: Sold for $58,500 CAD = $40,432 USD, 2009 Massey Ferguson 9795, w/4200 P/U Header., 1253 Sep Hours Cell B3 Specs: 3200 sep. hours, always stored inside, auto steer ready, excellent tires, pro drive unit, has power cast tail board for straw chopping, has 28' unloading auger, a/c works, used for wheat harvest this year. Does not sell with GPS unit-i.e. screen & globe I am trying to make column C return the "Seperator hours" from cells in column B. Column B has hundreds of cells with specs of machines and they all have the hours listed before the letters "sep" but sometimes the text "sep" has a capital S and sometimes it does not. The hours are also not always in the same place in the cell as shown above in the example of what two of the cells in the "B" column look like. I also need the result to be in number form so that I can use it in number formulas after the fact

 

[Asbestos_Jen]

Is the number of sep hours always 4 digits? If so, try this:

=--RIGHT(TEXTBEFORE(B3,"sep",1,1,0),5)

 

[dreid1011]

Another option for values longer or shorter than 4, and maybe there's a more elegant solution, but this seems to work:

=LET(t,TEXTBEFORE(LOWER(B2),"sep"),-LOOKUP(0,-RIGHT(t,ROW(INDEX(C:C,1):INDEX(C:C,LEN(t))))))

 

[Lancer7]

Not always 4 Jen but thank you! I should have mentioned that in my question as besides that it would have been perfect, Dreid that works perfect with it being 3 sometimes so thank you!

 

[dreid1011]

You're welcome, and happy to help.

 

[Lancer7]

Hey! Going back to this old post here, Your solution dreid worked great but I am having the odd cell give me issues where the number Im trying to bring back Is coming back as "0". It seems to always be ones that have two or more zeros on the end. A couple cell examples that I have returning 0 Sep hours are :

Specs:
SN: 1H0S680SPC0747721, 650/85R38 w/Duals, 2WD, 750/65R26 Chopper CM P Fold Bin Ext, Pro Drive, 26' Unload, Prem. Cab, Auto Trac Ready, GreenStar 3 7" Display, Yield Mon, 3400 Sep. Hours

Specs:
3000 Sep. Hours, Front Duals, Precision Ag Monitoring Upgrade System, Mud Hog 4WD System, Buddy Seat, Per Customers Sieve Replaced 2021

Specs:
Integrated Auto Steer, 2313 Engine Hours, 1600 Separator Hours, 3 Speed Manual Transmission, 4WD 2 Speed, Heavy Duty Variable Speed FH, Hi-Capacity FH Lift Cylinders, Premium Header Control, Greenstar Harvest Monitoring System (no display or receiver included), Integrated Auto Trac Steering Valve, Touchset Concave Adjustment, Large Round Bar Concave Ext Wear, Gen Purpose Chaffer, 6.9M Unloading Auger Hi-Capacity, Fine Cut Chopper w/ Tailboard Spreader, Heavy Duty Final Drives, Rear Tires 28L-26 Firestones, Brand New Fron Duals 520/85R42 Firestones, 110V Coolant Heater, Clean Grain Auger and Fan Bottom Protection Package, LED Upper Cab Lights, 6090 9L Engine, Inspected and Maintained Annually by Sydenstrickers in Mexico, Can Provide 5 Years Past Service Records, Front Shoe Auger Plate Has Been Updates in 70S Services, Has Mainly Been a Secondary Machine for Beans Only, Will Include a Headsight Kit to Run a Deere Draper

 

[Cubist]

Try:

Book2
	B	C
1
2	SN: 1H0S680SPC0747721, 650/85R38 w/Duals, 2WD, 750/65R26 Chopper CM P Fold Bin Ext, Pro Drive, 26' Unload, Prem. Cab, Auto Trac Ready, GreenStar 3 7" Display, Yield Mon, 3400 Sep. Hours	3400
3	3000 Sep. Hours, Front Duals, Precision Ag Monitoring Upgrade System, Mud Hog 4WD System, Buddy Seat, Per Customers Sieve Replaced 2021	3000
4	Integrated Auto Steer, 2313 Engine Hours, 1600 Separator Hours, 3 Speed Manual Transmission, 4WD 2 Speed, Heavy Duty Variable Speed FH, Hi-Capacity FH Lift Cylinders, Premium Header Control, Greenstar Harvest Monitoring System (no display or receiver included), Integrated Auto Trac Steering Valve, Touchset Concave Adjustment, Large Round Bar Concave Ext Wear, Gen Purpose Chaffer, 6.9M Unloading Auger Hi-Capacity, Fine Cut Chopper w/ Tailboard Spreader, Heavy Duty Final Drives, Rear Tires 28L-26 Firestones, Brand New Fron Duals 520/85R42 Firestones, 110V Coolant Heater, Clean Grain Auger and Fan Bottom Protection Package, LED Upper Cab Lights, 6090 9L Engine, Inspected and Maintained Annually by Sydenstrickers in Mexico, Can Provide 5 Years Past Service Records, Front Shoe Auger Plate Has Been Updates in 70S Services, Has Mainly Been a Secondary Machine for Beans Only, Will Include a Headsight Kit to Run a Deere Draper	1600
5	Sold for $58,500 CAD = $40,432 USD, 2009 Massey Ferguson 9795, w/4200 P/U Header., 1253 Sep Hours	1253
6	3200 sep. hours, always stored inside, auto steer ready, excellent tires, pro drive unit, has power cast tail board for straw chopping, has 28' unloading auger, a/c works, used for wheat harvest this year. Does not sell with GPS unit-i.e. screen & globe	3200
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=--TEXTAFTER("," &TEXTBEFORE(B2,"sep",,1),",",-1)

 

[Lancer7]

It did fix those issues! But seemed to cause some others in different cells that didn't have issues before, Seems to not work on cells that use comma's in the number say 2,000 instead of just 2000. And also any cells where how the data is put in slightly different

Specs:
Combine, PRWD (4wd), ONLY 18hrs! Pro Drive Transmission, Tristream Rotor, Power Folding Covers, 22.5ft Unloading Auger, 650/85R38 Duals, 750/65R26 Rear Tires, Premium Cab and Visibility Package, Chopper, 18 eng. / 1 sep. Hours,Basic Warranty Through October 26th 2024, Engine Warranty Through October 26th 2025 or 2000hrs, Emissions Warranty Through October 26th 2028 or 3000hrs.

Specs:
PRWD Combine, PowerPlus CVT Drive Trans. w/HD Planetary & Diff Lock, HD CVT Drive Feederhouse w/Lateral Tilt & Stone Trap, Hyd. Fore/Aft, Ext. Wear Rotor, MagnaCut Fine Cut Chopper w/Deluxe Spreader & Windrow Chute, High Capacity Grain Tank Extensions, High Capacity Unload for 40ft. Headers w/Independent Cross Auger Control, 620/70R42 Front Tires w/Duals, 750/65R26 Rear Tires, Luxury Cab w/Leather Seat, Pro 700 Display, NAVII Controller, 262 Receiver, HID Stadium Lighting, 3,890 engine/2,804 separator hours, SN YCG215135, Location: McLeansboro, IL, Stock # 177328

And also still some issues with cells I had returning as 0 :

Specs:
4WD Combine, Harvest Command Combine Automation, PowerPlus Variable CVT Drive w/Feed Rate, Lateral Tilt Feederhouse w/Stone trap & Hyd. Fore/Aft, MagnaCut Fine Cut Chopper w/Dual Disc Spreaders, Chrome Cone w/Std. Cage, High-Capacity Grain Tank w/ Power Folding Covers, In-Cab Adjust Pivoting Spout, Independent Cross Auger Control, 620/70R42 Front Tires w/ Duals, 750/65R26 Rear Tires, Michelin Tires, 2-Speed Powered Rear Axle, Luxury Cab w/Heated & Ventilated Red Leather Seat, Premium LED Light Package, Distance Lights, Full Guidance w/Pro 700 Display, Nav Controller, 372 Receiver, 640 Engine/520 Separator hours, SN YNG247539, Location: Hopkinsville, KY, Stock # 21540

 

[Cubist]

Try

Book1
	B	C
3	SN: 1H0S680SPC0747721, 650/85R38 w/Duals, 2WD, 750/65R26 Chopper CM P Fold Bin Ext, Pro Drive, 26' Unload, Prem. Cab, Auto Trac Ready, GreenStar 3 7" Display, Yield Mon, 3400 Sep. Hours	3400
4	3000 Sep. Hours, Front Duals, Precision Ag Monitoring Upgrade System, Mud Hog 4WD System, Buddy Seat, Per Customers Sieve Replaced 2021	3000
5	Integrated Auto Steer, 2313 Engine Hours, 1600 Separator Hours, 3 Speed Manual Transmission, 4WD 2 Speed, Heavy Duty Variable Speed FH, Hi-Capacity FH Lift Cylinders, Premium Header Control, Greenstar Harvest Monitoring System (no display or receiver included), Integrated Auto Trac Steering Valve, Touchset Concave Adjustment, Large Round Bar Concave Ext Wear, Gen Purpose Chaffer, 6.9M Unloading Auger Hi-Capacity, Fine Cut Chopper w/ Tailboard Spreader, Heavy Duty Final Drives, Rear Tires 28L-26 Firestones, Brand New Fron Duals 520/85R42 Firestones, 110V Coolant Heater, Clean Grain Auger and Fan Bottom Protection Package, LED Upper Cab Lights, 6090 9L Engine, Inspected and Maintained Annually by Sydenstrickers in Mexico, Can Provide 5 Years Past Service Records, Front Shoe Auger Plate Has Been Updates in 70S Services, Has Mainly Been a Secondary Machine for Beans Only, Will Include a Headsight Kit to Run a Deere Draper	1600
6	Sold for $58,500 CAD = $40,432 USD, 2009 Massey Ferguson 9795, w/4200 P/U Header., 1253 Sep Hours	1253
7	3200 sep. hours, always stored inside, auto steer ready, excellent tires, pro drive unit, has power cast tail board for straw chopping, has 28' unloading auger, a/c works, used for wheat harvest this year. Does not sell with GPS unit-i.e. screen & globe	3200
8	Combine, PRWD (4wd), ONLY 18hrs! Pro Drive Transmission, Tristream Rotor, Power Folding Covers, 22.5ft Unloading Auger, 650/85R38 Duals, 750/65R26 Rear Tires, Premium Cab and Visibility Package, Chopper, 18 eng. / 1 sep. Hours,Basic Warranty Through October 26th 2024, Engine Warranty Through October 26th 2025 or 2000hrs, Emissions Warranty Through October 26th 2028 or 3000hrs.	1
9	PRWD Combine, PowerPlus CVT Drive Trans. w/HD Planetary & Diff Lock, HD CVT Drive Feederhouse w/Lateral Tilt & Stone Trap, Hyd. Fore/Aft, Ext. Wear Rotor, MagnaCut Fine Cut Chopper w/Deluxe Spreader & Windrow Chute, High Capacity Grain Tank Extensions, High Capacity Unload for 40ft. Headers w/Independent Cross Auger Control, 620/70R42 Front Tires w/Duals, 750/65R26 Rear Tires, Luxury Cab w/Leather Seat, Pro 700 Display, NAVII Controller, 262 Receiver, HID Stadium Lighting, 3,890 engine/2,804 separator hours, SN YCG215135, Location: McLeansboro, IL, Stock # 177328	2804
10	4WD Combine, Harvest Command Combine Automation, PowerPlus Variable CVT Drive w/Feed Rate, Lateral Tilt Feederhouse w/Stone trap & Hyd. Fore/Aft, MagnaCut Fine Cut Chopper w/Dual Disc Spreaders, Chrome Cone w/Std. Cage, High-Capacity Grain Tank w/ Power Folding Covers, In-Cab Adjust Pivoting Spout, Independent Cross Auger Control, 620/70R42 Front Tires w/ Duals, 750/65R26 Rear Tires, Michelin Tires, 2-Speed Powered Rear Axle, Luxury Cab w/Heated & Ventilated Red Leather Seat, Premium LED Light Package, Distance Lights, Full Guidance w/Pro 700 Display, Nav Controller, 372 Receiver, 640 Engine/520 Separator hours, SN YNG247539, Location: Hopkinsville, KY, Stock # 21540	520
Sheet6

Cell Formulas
Range	Formula
C3:C10	C3	=LET(t,TEXTSPLIT(SUBSTITUTE(B3,", ","|"),{"|","/"," "},,1),--INDEX(t,XMATCH(TRUE,ISNUMBER(SEARCH("sep",t)))-1))

 

[Cubist]

You don't need SUBSTITUTE.

=LET(t,TEXTSPLIT(B3,{", ","/"," "},,1),--INDEX(t,XMATCH(TRUE,ISNUMBER(SEARCH("sep",t)))-1))