Tell Excel to parse cells with units of measurement as numbers, including multiplier

Question

I have some Excel files containing cells with values like "20 mA", "3.13 µA" , "1.66 mV" and similar.

Would it be possible to somehow ignore the last character ('A' or 'V') and then multiply the number with the suffix automatically?

For example 20 mA should result in 0.02

Are they all the same structure - so number followed by space, followed by a two character unit with the multiplier as the first unit? Are they all either micro or milli? — Paul, Dec 2, 2011 at 11:37
if you want the cells identified and replaced then this is really a VBA question — brettdj, Dec 2, 2011 at 12:05
@Paul yeah they are same format, actually there are multiple columns and each column is either 'A' or 'V' as for current and voltage. — Dumbo, Dec 2, 2011 at 12:09
@brettdj I dont want the numbers be replaces, just when I do for example a SUM, I want the sum to calculate the real numbers. — Dumbo, Dec 2, 2011 at 12:10
what I meant was if you have data in A1 that you want updated in A1 then this is best done with code. — brettdj, Dec 3, 2011 at 0:24

JMax · Accepted Answer · 2011-12-03 11:42:48Z

3

You could try this kind of formula (if your formula is in A1):

=CONVERT(VALUE(MID(A1,1,FIND(" ",A1))), MID(A1,FIND(" ",A1)+1,LEN(A1)), LEFT(A1,1))

Caveats:

answered Dec 2, 2011 at 15:33

JMax

3,2151 gold badge23 silver badges20 bronze badges

Is CNUM() the same thing as VALUE() in the American version of Excel?
– Excellll
Dec 2, 2011 at 18:55
+1 nicely done with a formula that is far too under utilised.
– brettdj
Dec 3, 2011 at 0:25
@Excellll: yes it is, sorry, I missed this part of the "formula translation". I will edit my post
– JMax
Dec 3, 2011 at 11:42
nice solution. but seems only for excel 2007+
– wilson
Dec 6, 2011 at 6:55

Add a comment |

1 Answer 1