Removing text from numbers

[ucmexcel]

I have a column of data which has numbers and units (small example below). I need to remove the text (units) and at the same time multiply the number by a value which is based on what the text is:

-999.9uA needs to become -999.9*10^-6

-98.40mA needs to become -98.40*10^-3

etc. Can anyone help?

Thanks!

[Trebor76]

Hi ucmexcel,

Welcome to the forum.

Have a look at the attached where I've created a User Defined Function (udf) and used Excel's VLOOKUP* function to achieve your desired result (hopefully).

HTH

Robert

* I made the assumption that the text multiplier are always the last two characters of the string.

[Mark@Work]

As an alternative, try:

Please Login or Register  to view this content.

LEFT(A1,LEN(A1)-1) strips off the Amps
Then the two substitutes replace m by E-3 and u by E-6

VALUE then converts the new text string to the required number.

Mark

Note that if the units are always going to be A then this can be simplified to:

Please Login or Register  to view this content.

Mark

[ucmexcel]

Thanks to both of you! I really appreciate the help.