Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
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 '11 at 11:37
if you want the cells identified and replaced then this is really a VBA question – brettdj Dec 2 '11 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. – Sean87 Dec 2 '11 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. – Sean87 Dec 2 '11 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 '11 at 0:24
up vote 3 down vote accepted

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))


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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .