Extract number from text string
I'd like to extrapolate all the numbers inside a text string (based on a condition) and if the numbers are separated by a text they should be separated too. Below an example to better understand since the problem it's very complex
INPUT OUTPUT1 OUTPUT2 1 500ML BIA BINGWA SORGHUM 1 500 2 CLUB 07 VODKA LEMON 24X330ML 24 330 3 375ML EAGLE LAGER REGULAR RB 375X20ML 20 375 4 KONYAGI GIN 1X500ML 1 500 5 SAFARI LAGER NRB 24x330 LOCAL 24 330
- In row 1 the OUTPUT1 = 1 even there is no 1x500ML but only 500ML
- Row 3 have reverse order, instead of 20x375ML -> 375x20ML (I think that this can be overcomed with "put smallest in OUTPUT1 and Largest in OUTPUT2)
- Row 5 doesn't have ML so the recognition should be made by the x used as separator
both formula and VBA approaches are fine.
This is error prone but it does give you a starting point to spot check the 200
Using excel formula's you could do something like this; but each record would still have to be spot checked. For example if a product has an X in the name beyond the 24x330 then it would error or provide wrong values. The data is just too unstable to generate a true program to do this and account for all possibilities. We're not saying it can't be done, simply saying the time would be better spent manually doing it.
As you can see I couldn't easily handle row 3:
Formula for E: =IFERROR(MID(B1,FIND("X",UPPER(B1))-2,2),1)*1
Formula for F: =MID(B1,IF(FIND("ML",UPPER(B1))<6,1,FIND("ML",UPPER(B1))-3),3)