Numbers formula question

It would be best if you used two columns for the range of mg per kg (one for each number) but it can be done from one. A couple of questions first:

1. Will ALL of these cells be of the form "number to number blah per blah" or will some just be "number blah per blah"? or will there be other variations?
2. Will the dosage ALWAYS be mg per kg and weight will ALWAYS be kg? The larger question is if you need any checking of the units so you don't do something like multiply mg/g x kg and say the result is mg

If you are not dead set on doing it as stated in your post, I recommend you do the following:

• Use two columns for your dosage numbers vs trying to parse the text to extract them
• The two column headers have "Min Dosage (mg per kg)" and "Max Dosage (mg per kg)".
• The cells in the columns will be a plain number without "mg per kg"
• The weight column header have "Weight (kg)".
• The cells in the weight column will be plain numbers without the "kg"

But it will be fun creating the formula for your original way if you want to do it that way.

Don't worry about the error triangles, you are going to hide those columns.

C2 =VALUE(REGEX.EXTRACT(A2,"[0-9,.]+",1))×B2

D2 =VALUE(REGEX.EXTRACT(A2,"[0-9,.]+",2))×B2

E2 =IF(A2≠"",C2&IFERROR(" to "&D2,"")&" mg","")

Fill down with those three formulas to complete the columns

Hide columns C and D

Alternatively, you can do this without column C and D by cut/pasting their formulas in place of the C2 and D2 in the formula in E2. I put them in separate columns so I could format the results with a thousands separator.

The formulas in C2 and D2 assume numbers consist only of the characters 0-9, comma, and dot/period. Some places use a space as a thousands separator and the formulas will fail on that.