Extract some (not all) numbers from these strings

[Yamezz]

I have the following output in a csv file that I'd like to extract the number of bottles from.

2022 Sparkling Tempranillo Rosé - 1 Bottle
2022 Limited Edition Sparkling Vermentino Case - 3 Bottles
2022 Sparkling Tempranillo Rosé Case - 6 Bottles
2022 Limited Edition Sparkling Vermentino Case - 12 Bottles

I have used =MID(A2,FIND(" Bottle",A2)-2,2) to get part way there, but I'm left with a leading space in the single digit results and what looks to be text instead of a numeral when I get "12" for the last example.

I've simply run out of brain power to get any further. Can anyone help?

 

[Alex Blakenburg]

Just adding a substitute and increasing the number of characters in Peter's post 8 formula will get you to 9999.

=--SUBSTITUTE(MID(A2,FIND(" Bottle",A2)-4,4),"-","")

 

[Peter_SSs]

will get you to 9999

Good thinking. Got me pondering about other short ways. This would also get up to 4 digits ..

=ABS(MID(A2,FIND(" Bottle",A2)-4,4))

 

[Dan_W]

Not pretty, but...

=MID(SUBSTITUTE(SUBSTITUTE(A1," Bottles"," Bottle")," Bottle",""),FIND(" - ",A1)+3, 9999)