So, I have two columns where both might possibly contain the numbers I want to extract to a third column in the same row. The numbers I am looking to extract will allways be followed by a "G", an "L" or a "KG". As G denotes grams it would be great if the numbers extracted before a G would come out with the comma moved three spaces forward, i.e. so that 24G would become 0,0024 in the third column.

In the first column, A, there will only be the unit of measurement preceded by the number, but in many cases column A will be empty (see table for data). In those cases I'd like to check the longer text/number string in column B, same row, for the numbers. In column B the number might be preceeded by another number and an X like this: 8X150G. In those cases I would like only the 150 to be extracted.

The commas are not a problem as my country for some reason has choses a different standard than the English one.

Having tried on my own to no avail I realise that this requires quite a bit of work so all help is much appreciated, even only for the first column. A formula would be preferable but I am of course open to VBA as well.

I am using Excel 2013 on Windows 8.

In the first column, A, there will only be the unit of measurement preceded by the number, but in many cases column A will be empty (see table for data). In those cases I'd like to check the longer text/number string in column B, same row, for the numbers. In column B the number might be preceeded by another number and an X like this: 8X150G. In those cases I would like only the 150 to be extracted.

The commas are not a problem as my country for some reason has choses a different standard than the English one.

Having tried on my own to no avail I realise that this requires quite a bit of work so all help is much appreciated, even only for the first column. A formula would be preferable but I am of course open to VBA as well.

I am using Excel 2013 on Windows 8.

A B

2,5KG

<tbody>

</tbody>

STORFE HØYRYGG STRIMLET GILDE

<tbody>

</tbody>

0,7L

<tbody>

</tbody>

BLÅBÆRSAFT 0,7L LERUM

<tbody>

</tbody>

3 KG JARLSBERG 27% KUVERT

<tbody>

</tbody>

419863 SALAMI SKIVET 8X150G ENH

<tbody>

</tbody>

431087 LAM LAPSKAUSKJØTT SALT TERNET FRYST

<tbody>

</tbody>

150G

<tbody>

</tbody>

NORVEGIA 27% SKIVET 150G TINE

<tbody>

</tbody>

468351 SVINEBOG SALT KOKT TERNET 5KG

<tbody>

</tbody>24G BRINGEBÆRSYLTETØY KUVERT 24G LERUM

200STK

<tbody>

</tbody>

SMØR KUVERT 12G

<tbody>

</tbody>

5KG

<tbody>

</tbody>

RØKT KJØTTPØLSE UTEN SKINN 2X2,5KG

<tbody>

</tbody>

0,9L

<tbody>

</tbody>

HUSHOLDNINGSSAFT U/SUKKER 0,9L LERUM

<tbody>

</tbody>

<tbody>

</tbody>

Last edited: