# Extracting "non uniform" numbers from a string



## BigGee (Tuesday at 4:53 PM)

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 RelayProduction Stage - 100 x Heater RelayProduction Stage - 40 x MM LiteProduction Stage - 60 x PCM batch 2

Thanks in anticipation

Neil


----------



## Micron (Tuesday at 5:06 PM)

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 (Tuesday at 5:13 PM)

T202301a.xlsmBCD12Production Stage - 100 x Heater Relay100 Heater Relay3Production Stage - 60 x PCM batch 260 PCM batch 241dCell FormulasRangeFormulaC2:C3C2=--MID(B2,FIND("-",B2)+2,3)D2:D3D2=MID(B2,FIND("x",B2)+1,99)


----------



## BigGee (Wednesday at 9:11 AM)

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 (Wednesday at 9:22 AM)

How about

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


----------



## Dave Patton (Wednesday at 11:02 AM)

or if numbers could be larger or smaller

T202301a.xlsmBC2Production Stage - 100 x Heater Relay1003Production Stage - 60 x PCM batch 2604Production Stage - 1 x PCM batch 215Production Stage - 100000 x PCM batch 2100,00061dCell FormulasRangeFormulaC2:C5C2=--MID(B2,FIND("-",B2)+2,LEN(B2)-33)


----------



## BigGee (Yesterday at 3:34 AM)

Thank you ver


Dave Patton said:


> or if numbers could be larger or smaller
> 
> T202301a.xlsmBC2Production Stage - 100 x Heater Relay1003Production Stage - 60 x PCM batch 2604Production Stage - 1 x PCM batch 215Production Stage - 100000 x PCM batch 2100,00061dCell FormulasRangeFormulaC2:C5C2=--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 (Yesterday at 4:02 AM)

What about this one?

23 01 13.xlsmBC12Production Stage - 100 x Heater Relay1003Production Stage - 60 x PCM batch 2604Production Stage - 1 x PCM batch 215Production Stage - 100000 x PCM batch 21000006Production Stage - 2500 x T&T sensor2500Get numCell FormulasRangeFormulaC2:C6C2=MID(B2,19,FIND(" x",B2)-19)+0


----------



## Dave Patton (Yesterday at 8:59 AM)

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.xlsmABCDE12Production Stage - 100 x Heater Relay100100 Heater Relay3Production Stage - 60 x PCM batch 26060 PCM batch 24Production Stage - 1 x PCM batch 211 PCM batch 25Production Stage - 100000 x PCM batch 2100,000100,000 PCM batch 26Production Stage - 2500 x PCM sensor2502,500 PCM sensor7Production Stage - 2500 x sen#VALUE!2,500 sen8Production Stage - 2500x sen#VALUE!#VALUE! sen9Production Stage - 2500 y sen 929#VALUE!#VALUE!#VALUE!10Production Stage - 2500 Excel 3652,500Excel 3651dCell FormulasRangeFormulaD2:D9D2=--MID(B2,20,FIND(" x",B2)-19)E2:E9E2=MID(B2,FIND("x",B2)+1,99)D10D10=--TEXTBEFORE(TEXTAFTER(B10," ",3)," ",1)E10E10=TEXTAFTER(B10," ",4)C2:C9C2=--MID(B2,FIND("-",B2)+2,LEN(B2)-33)


----------

