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?

 

[SanjayGMusafir]

Check this and revert - This shall replace space and convert the result to number

=SUBSTITUTE(MID(A2,FIND(" Bottle",A2)-2,2)," ","")*1

 

[Dan_W]

Here is another way of solving it, but it's really no different from Sanjay's solution - it just trims result (ie, removes the leading space) and then converts the resulting string into a value:

=VALUE(TRIM(MID(A2,FIND(" Bottle",A2)-2,2)))

 

[Yamezz]

Many ways to skin a cat!

Thank you both.

 

[SanjayGMusafir]

Glad to help you and thanks for the feedback.

 

[Peter_SSs]

There is no need to either TRIM or SUBSTITUTE the space. Your original formula with a +0 would be simpler (assuming the number of bottles is never 100 or more)

=MID(A2,FIND(" Bottle",A2)-2,2)+0

BTW, there is a newer version of XL2BB available. You might want to consider updating.

 

[Yamezz]

There is no need to either TRIM or SUBSTITUTE the space. Your original formula with a +0 would be simpler (assuming the number of bottles is never 100 or more)

=MID(A2,FIND(" Bottle",A2)-2,2)+0

BTW, there is a newer version of XL2BB available. You might want to consider updating.

Thanks Peter. How does this work - does the +0 just force Excel to convert the string result to a number?

 

[Peter_SSs]

does the +0 just force Excel to convert the string result to a number?

Yes, and in doing so automatically removes any leading or trailing spaces, hence my comment above about not needing TRIM or SUBSTITUTE.
Instead of +0 you could also use *1 or also do it like this.

=--MID(A2,FIND(" Bottle",A2)-2,2)

 

[Scott Huish]

That will fail if there are over 99 bottles.

=MAX(IFERROR(--MID(MID(A1,FIND("-",A1)+2,255),1,ROW(INDIRECT("1:15"))),0))

 

[Peter_SSs]

That will fail if there are over 99 bottles.

I did mention that already.

(assuming the number of bottles is never 100 or more)

Given the type of data & the fact that the question was only about getting a numerical value rather than text from the original formula, my guess that >99 will not occur for the OP.
However if that were possible, I provide the following comments.

=MAX(IFERROR(--MID(MID(A1,FIND("-",A1)+2,255),1,ROW(INDIRECT("1:15"))),0))

That will fail if a hyphen occurs before the 'relevant' hyphen as shown in rows 6 & 7 below.
That could be overcome, and the volatility removed, with a formula structure like column J.
Column K offers a slightly shorter alternative.

23 12 18.xlsm
	A								I	J	K
1
2	2022 Sparkling Tempranillo Rosé - 1 Bottle								1	1	1
3	2022 Limited Edition Sparkling Vermentino Case - 3 Bottles								3	3	3
4	2022 Sparkling Tempranillo Rosé Case - 6 Bottles								6	6	6
5	2022 Limited Edition Sparkling Vermentino Case - 12 Bottles								12	12	12
6	2009 Chateau Haute-Madrac - 864 Bottles per crate								0	864	864
7	2019-20 Some wine case - 6 Bottles								0	6	6
Bottles

Cell Formulas
Range	Formula
I2:I7	I2	=MAX(IFERROR(--MID(MID(A2,FIND("-",A2)+2,255),1,ROW(INDIRECT("1:15"))),0))
J2:J7	J2	=MAX(IFERROR(--MID(MID(A2,FIND(" - ",A2)+3,255),1,{1,2,3,4,5,6}),0))
K2:K7	K2	=--REPLACE(LEFT(A2,FIND(" Bottle",A2)),1,FIND(" - ",A2)+2,"")

Columns J & K above would also fail if " - " occurred before the 'relevant' " - " as shown below, where yet another alternative is provided should that be possible with the data.

23 12 18.xlsm
	A								I	J	K	L
9	2009 Chateau Haute-Madrac - French - Case - 864 Bottles								0	0	#VALUE!	864
Bottles

Cell Formulas
Range	Formula
I9	I9	=MAX(IFERROR(--MID(MID(A9,FIND("-",A9)+2,255),1,ROW(INDIRECT("1:15"))),0))
J9	J9	=MAX(IFERROR(--MID(MID(A9,FIND(" - ",A9)+3,255),1,{1,2,3,4,5,6}),0))
K9	K9	=--REPLACE(LEFT(A9,FIND(" Bottle",A9)),1,FIND(" - ",A9)+2,"")
L9	L9	=--RIGHT(SUBSTITUTE(LEFT(A9,FIND(" Bottle",A9))," - ",REPT(" ",15)),15)