Converting Alphanumeric text to usable number value

[Willingtolearn]

Hello,

I have a need to convert alphanumeric text into number value.

Our system imports alphanumeric text for values from our supply software and I need to convert these text values into numerical values. The system uses alphabetical substitutes for large numbers, i.e. M = Million, K = Thousands
Example:
Part A : 4.326M
Part B : 168.655K
Part C : 79.461K
Part D : 1.21M

What I would like to do is convert these to numerical values in a new column within Excel so that I can work with them.

Part A : 4.326M 4,326,000
Part B : 168.655K 168,655
Part C : 79.461K 79,461
Part D : 1.21M 1,210,000

I have looked through the postings but did not see any that addresses this issue. If I have missed the posting, kindly point me in the correct direction.

Thank you for the help.

 

[Rick Rothstein]

Are those values (for example, this one... Part A : 4.326M) all in a single cell?

Also, is a VBA macro solution acceptable to change the values in place?
Or did you want a formula to put the converted value into an adjacent cell?

 

[Willingtolearn]

Rick,

Thank you for the response. As per your questions:
1) The value string in my example would be in two cells; Cell one is just "Part A :" Cell two is just 4.326M. I am interested in converting 4.326M.
2)I would like to have a formula that puts the converted value into an adjacent cell

Thank you for your help.

 

[Rick Rothstein]

Assuming your first value is in cell B1, put this formula in cell C1 and copy it down...

=LEFT(B1,LEN(B1)-1)*IF(RIGHT(B1)="K",1000,IF(RIGHT(B1)="M",1000000,1))

Note: You will have to Custom Format the cells using #,##0 in order to get the numbers to display with commas.

 

[Willingtolearn]

Thank you Rick. That worked. Much Appreciated