Extracting "non uniform" numbers from a string

[BigGee]

Hello,

I have tried to the best of my personal abilities (which is not as good as I had hoped) to try and solve this issue but I cant get a solution, but I am absolutely sure that it is not too complicated and therefore hope that someone can help me?
In the table below I want to extract into 2 adjacent columns a. the quantity and b. the description (ideally excluding anything after the description, in the example batch 2) . I have tried LEFT, RIGHT and MID but fallen foul of the fact that the no. of characters vary.
I have seen lots of clever things people have done in similar scenarios but havent really understood the concept so was hopeful someone might be able to help out?

Production Stage - 100 x Heater Relay

Production Stage - 100 x Heater Relay

Production Stage - 40 x MM Lite

Production Stage - 60 x PCM batch 2

Thanks in anticipation

Neil

 

[Micron]

Is this about VBA? Then perhaps InStr function will help with the variances. Based on the 4 rows you show,
- use it to find "-" and add 1
- use Val with Mid will return only the numbers that follow
- find x and add 1
- then Mid again to get the rest
The problem would be how to strip off anything after the point you want, which isn't clear to me. An example of that is PCM and not PCM batch or PCM batch 2?

 

[Dave Patton]

T202301a.xlsm
	B	C	D
1
2	Production Stage - 100 x Heater Relay	100	Heater Relay
3	Production Stage - 60 x PCM batch 2	60	PCM batch 2
4
1d

Cell Formulas
Range	Formula
C2:C3	C2	=--MID(B2,FIND("-",B2)+2,3)
D2:D3	D2	=MID(B2,FIND("x",B2)+1,99)

 

[BigGee]

That is great thankyou. Only slight issue was that in the event that there is a qty of 1 (or a single digit) then it returns the x

 

[Fluff]

How about

=--SUBSTITUTE(MID(B2,FIND("-",B2)+2,3),"x","")

 

[Dave Patton]

or if numbers could be larger or smaller

T202301a.xlsm
	B	C
2	Production Stage - 100 x Heater Relay	100
3	Production Stage - 60 x PCM batch 2	60
4	Production Stage - 1 x PCM batch 2	1
5	Production Stage - 100000 x PCM batch 2	100,000
6
1d

Cell Formulas
Range	Formula
C2:C5	C2	=--MID(B2,FIND("-",B2)+2,LEN(B2)-33)

 

[BigGee]

Thank you ver

or if numbers could be larger or smaller

T202301a.xlsm
	B	C
2	Production Stage - 100 x Heater Relay	100
3	Production Stage - 60 x PCM batch 2	60
4	Production Stage - 1 x PCM batch 2	1
5	Production Stage - 100000 x PCM batch 2	100,000
6
1d

Cell Formulas
Range	Formula
C2:C5	C2	=--MID(B2,FIND("-",B2)+2,LEN(B2)-33)

thank you very much for this. It sort of works but I have come across a very strange scenario where it works on some fields but not on others. On the ones where it doesnt work, it sometimes, but not always, works if I edit the cell number and then edit it back to the original. Here is the examples of 2 cells - I know it might be something really obvious that I am missing or have done wrong but I can't spot it. The only thing that is unusual about what I am trying to do is that I was trying to run the formula on a "calculated" field - one taken from another location so the field had a formula in it - that didnt work so I copied the content of the field as a paste special as text - and like I say it worked in some but not all instances, even though I just dragged the formula down.

 

[Peter_SSs]

What about this one?

23 01 13.xlsm
	B	C
1
2	Production Stage - 100 x Heater Relay	100
3	Production Stage - 60 x PCM batch 2	60
4	Production Stage - 1 x PCM batch 2	1
5	Production Stage - 100000 x PCM batch 2	100000
6	Production Stage - 2500 x T&T sensor	2500
Get num

Cell Formulas
Range	Formula
C2:C6	C2	=MID(B2,19,FIND(" x",B2)-19)+0

 

[Dave Patton]

Try using Formula Evaluate (Formulas Formula Evaluate) to review the formulas.
find shows the - at position 18 +2 means first number is at position 20 If the "normal" text has unit of 1 the len is 34, the text with 2500 is 37 less 33 means number is 4 characters. This requires text like your examples.
Note "sensor" is shorter than batch 2; consequently, use Peter's suggestion. N.B. The formula depends on the unique character "x" at the position shown.
You could build the formula to search for the position of a space after the first number.
This is easier with Excel 365.

T202301a.xlsm
	A	B	C	D	E
1
2		Production Stage - 100 x Heater Relay	100	100	Heater Relay
3		Production Stage - 60 x PCM batch 2	60	60	PCM batch 2
4		Production Stage - 1 x PCM batch 2	1	1	PCM batch 2
5		Production Stage - 100000 x PCM batch 2	100,000	100,000	PCM batch 2
6		Production Stage - 2500 x PCM sensor	250	2,500	PCM sensor
7		Production Stage - 2500 x sen	#VALUE!	2,500	sen
8		Production Stage - 2500x sen	#VALUE!	#VALUE!	sen
9		Production Stage - 2500 y sen 929	#VALUE!	#VALUE!	#VALUE!
10		Production Stage - 2500 Excel 365		2,500	Excel 365
1d

Cell Formulas
Range	Formula
D2:D9	D2	=--MID(B2,20,FIND(" x",B2)-19)
E2:E9	E2	=MID(B2,FIND("x",B2)+1,99)
D10	D10	=--TEXTBEFORE(TEXTAFTER(B10," ",3)," ",1)
E10	E10	=TEXTAFTER(B10," ",4)
C2:C9	C2	=--MID(B2,FIND("-",B2)+2,LEN(B2)-33)