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)

Need Your Help

how to stop kibana (not as a service)?

linux kibana elasticsearch-plugin

I am trying to stop kibana on SSH with kill but it respawns immediatly

Character encoding for US Census Cartographic Boundary Files

unicode encoding character shapefile census

I'm trying to import the US Census cartographic boundary files (available here: http://www.census.gov/geo/www/cob/bdy_files.html ) into a GeoDjango application. However, python is complaining about