Cell Text to Number (based on Cell Text Value)

Question

I have the following table:

2.0 M
1.9 M   
1455.0 K    
1386.9 K

Is there a way that I can convert that table into Numerical values based on the Letter at the end of the table such that the following is output:

2.0 M   2,000,000
    1.9 M   1,900,000
    1455.0 K    1,455,000
    1386.9 K    1,386,900

so: Remove the letter and multiply by either 1,000,000 or 1,000

Thanks,

Is the letter in the cell (column A) with the number or is the letter in another column cell? — Chrismas007, Jan 12 '15 at 17:32

score 2 · Accepted Answer · answered Jan 12 '15 at 20:10

2

Or this one (doesn't matter how many spaces):-

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"K","E3"),"M","E6"))

answered Jan 12 '15 at 20:10

Tom Sharpe

2

Thank you! I had seen a similar approach by @barry houdini and copied from the master http://stackoverflow.com/questions/27784048/summing-numeric-portion-of-cell-when-alpha-portion-of-cell-is-the-same/27784868#27784868 – Tom Sharpe Jan 12 '15 at 22:12

score 0 · Answer 2 · answered Jan 12 '15 at 17:17

0

Please try:

=IF(RIGHT(A1)="M",LEFT(A1,FIND(" ",A1))*1000000,LEFT(A1,FIND(" ",A1))*1000)

answered Jan 12 '15 at 17:17

pnuts

score 0 · Answer 3 · answered Jan 12 '15 at 17:22

0

With data in column A, in B1 enter:

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

For example:

enter image description here

answered Jan 12 '15 at 17:22

Gary's Student

3 Answers3