Remove units to express a concentration or strength as just a number

**jgwhite** · 10-17-2012, 06:34 AM

I have a column of various concentrations (strengths) of medicines eg.

10mg/5ml
50mg/ml
500mcg/5ml
1mmol/ml

I require a formula that will remove the units and express the resulting fraction as a number, so for the above examples, this would be:

2
50
100
1

Can any one please help. Thanks.

**twiggywales** · 10-17-2012, 06:49 AM

bit messy but

Please Login or Register  to view this content.

(entered with CTRL+SHIFT+ENTER)

see attached book seems to work

example.xlsx

i just realised i left Colmn B on that can be deleted it was just an interim cell while i confirmed it working

**jgwhite** · 10-17-2012, 07:47 AM

Thanks Twiggy.
This seems to work on your example sheet, but when I copy the formula into mine I get a #VALUE! error. Also, when I enter more data and drag the formula further down the column on your example sheet I get a #NAME? error. Any idea what I'm doing wrong?
PS - what is the purpose of the section of the formula $A$65:$A$90? - is the actual reference critical?

**twiggywales** · 10-17-2012, 07:56 AM

did you enter it with control+Shift+enter not just enter its an array formula.

and that referace is just telling what rows to use in the char part

as in

=CHAR(ROW(A65)) will return A
so in an array CHAR(ROW(A65:A90) means ABCDEFGHIJKLMNOPQRSTUVWXYZ