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



## MikeBor (Dec 30, 2022)

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


----------



## Flashbond (Dec 30, 2022)

```
=SUBSTITUTE(SUBSTITUTE(A1, "K", "0"), "M", "0000")
```


----------



## MikeBor (Dec 30, 2022)

I'm sorry, but I would like to re-phrase the request.

I would like to change the numbers that end in "K" to represent numbers in the "thousands" and letters that end in "M" to represent numbers in the "millions....with appropriate number of digits.  

Thank You


----------



## Flashbond (Dec 30, 2022)

Yes, it does what you've requested.


----------



## MikeBor (Dec 30, 2022)

Flashbond said:


> ```
> =SUBSTITUTE(SUBSTITUTE(A1, "K", "0"), "M", "0000")
> ```


I'm sorry, but I would like to re-phrase the request.

I would like to change the numbers that end in "K" to represent numbers in the "thousands" and letters that end in "M" to represent numbers in the "millions....with appropriate number of digits.

Thank You


----------



## MikeBor (Dec 30, 2022)

Flashbond said:


> Yes, it does what you've requested.


I'm sorry but the period in each number needs to be removed, so that I can correctly add the sum of the digits.  In the current way....The "millions" would be added as singular numbers because of the period.


----------



## Flashbond (Dec 30, 2022)

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


----------



## Joe4 (Dec 30, 2022)

Is this what you are looking for?

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

Note: Flashbond, I think the issue is that your formula adds the 0's after the decimal, not before it.  So it doesn't ever change the value by the multiples they are looking for (essentially, it will return the same number when it is K or M).

Of course, this is all assuming that the decimal point is being used as a decimal, and not a thousands separator in their regional settings.


----------



## MikeBor (Dec 30, 2022)

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


----------



## MikeBor (Dec 30, 2022)

This was the correct formula...missed added the additional zero's to the "M" 

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


----------



## MikeBor (Dec 30, 2022)

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


----------



## Flashbond (Dec 30, 2022)

Flashbond said:


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


I still think this is much more efficient.


----------



## Joe4 (Dec 30, 2022)

Flashbond said:


> 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 (Dec 30, 2022)

MikeBor said:


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


----------



## Flashbond (Dec 30, 2022)

Then it would be:

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


----------



## MikeBor (Dec 30, 2022)

Flashbond said:


> 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 (Dec 30, 2022)

Flashbond said:


> 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 (Jan 1, 2023)

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 (Jan 1, 2023)

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


----------

