Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Caveats:

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.