Reprocess data with strings and Numbers in Google Sheets Or Excel

Question

I have this data in Google sheets Or Excel:

2 MG/ML
500 MCG
100 MG ; 200 MG ; 200 MCG
100 MCG/ML
325 MG ; 37.5 MG
60 G ; 9 G/1000 ML

I would like to have it this format. i think Regex expression could do? How i Can acheive that?:

all data                       Dose                Unit 
2 MG/ML                          2                 MG/ML
500 MCG                         500                MCG
100 MG ; 200 MG ; 200 MCG       100;200;200        MG;MG;MCG
100 MCG/ML                      100                MCG/ML
325 MG ; 37.5 MG                325;37.5           MG;MG
60 G ; 9 G/1000 ML              60;9               G;G/1000 ML

score 3 · Answer 1 · answered Jul 22 '20 at 09:08

3

In B2 you could use:

=REGEXREPLACE(A2,"(;)?(?:^| +)([\d.]+)[ A-Z\/\d]+","$1$2")

See the online demo

In C2 you could use:

=REGEXREPLACE(A2,"(?:^|\s*(;))\s*[\d.]+\s*","$1")

See the online demo

Drag both down.

answered Jul 22 '20 at 09:08

JvdV

41,931
5
24
46

1

Wow save my life! Thanks! – Simon GIS Jul 22 '20 at 09:11
@SimonGIS, you are welcome. If it has answered the question feel free to accept it as such (or the other answer offcourse). – JvdV Jul 23 '20 at 07:28

score 2 · Answer 2 · answered Jul 22 '20 at 09:34

@JvdV has come up with a great solution. Another way to solve this is to make your own custom functions with Google Apps Script, in case you need more flexibility down the line:

function GETDIGITS(inputString) {
  return inputString.toString().match(/(?<=^|\s)([\d.]+)(?=\s|$)/g).join(";");
}

function GETUNITS(inputString) {
  return inputString.toString().split(";").map(str => str.match(/[A-Z]+(\/[A-Z0-9\s]+)?/g)).join(";");
}

Demo

Reprocess data with strings and Numbers in Google Sheets Or Excel

2 Answers2