I have numbers with a letter at the end. I would like to change that letter to a number.

[MikeBor]

I have daily downloads of a file that place a "K" or an "M" at the end of a number to represent "thousand" or "million". I would like to change the "K" and "M" to numbers. The "K" would add "one zero" to the end of the number and the "M" would add 4 zero's to the end of the number. Example shown below...

Thank You

 

[Legacy 143009]

=SUBSTITUTE(SUBSTITUTE(A1, "K", "0"), "M", "0000")*1

I still think this is much more efficient.

 

[Joe4]

I still think this is much more efficient.

It doesn't return the correct values.
Compare your formula to the other one, and you will see the difference.
All your formula does is remove the "M" or "K", it doesn't change/increase the value at all.

 

[Joe4]

Thank You So Much!! You were very close....but I saw what I could do to get the correct formula....This is what worked.....I just had to add a couple of zero's...

=IF(RIGHT(A1,1)="K",SUBSTITUTE(A1,"K","")*1000,IF(RIGHT(A1,1)="M",SUBSTITUTE(A1,"M","")*10000,A1))

You are welcome.
Sorry, I confused some of the replies, and picked up the multipliers of 10 and 10000 from the other post.

 

[Legacy 143009]

Then it would be:

=SUBSTITUTE(SUBSTITUTE(A1, "K", "000"), "M", "000000")+0

 

[MikeBor]

I still think this is much more efficient.

Flashbond, I appreciate your help for sure......this screenshot may help show the differences.....
Column A = The Original Number
Column B = Your formula....but it does not allow me to add a comma and it does not convert the "millions" into 7 digit numbers and the "thousands" into 4 digit numbers.
Column C = What I was looking for so that I could add series of numbers.

My explanation of what I needed may not have been the best representation of what I was looking to do. If that was the case....Sorry for that and thanks for all of your help.

 

[Joe4]

Then it would be:

=SUBSTITUTE(SUBSTITUTE(A1, "K", "000"), "M", "000000")+0

Still doesn't work. Have you actually tried them and compared the results?

The only way I can think of you where yours would work if the periods were treated as thousands separators and not decimal points.

 

[Peter_SSs]

If your samples are representative and every value has 2 decimal places and ends with K or M, then wouldn't this suffice?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",""),"K",0),"M","0000")+0

 

[shinigamilight]

B1: =VALUE(SUBSTITUTE(IFERROR(IF(SEARCH("K",A1),SUBSTITUTE(A1,"K",0)),SUBSTITUTE(A1,"M","0000")),".",""))