Replace ending zeroes in a list of numbers by 1

[zicitroj]

Hi all,

I am using Excel 2007. I have a column containing a long list of 5 digit numbers and some of those numbers end with zero. I need to replace those ending zeroes by 1.
​

For example; if the number is 11250, I want it to become 11251.

Anyone can help?

regards,
Ali

 

[Dryver14]

try this

=IF(RIGHT(A1,1)="0",REPLACE(A1,5,1,1),A1)+0

 

[Krishnakumar]

Hi,

=A1+(MOD(A1,10)=0)

 

[zicitroj]

ok, thanks a lot guys! It works but it leaves me with a small problem. In fact, I have a few numbers which also starts with zero (eg: 05210) and when I apply the formula, the number becomes 5211. I want Excel to keep the zero at the beginning for such numbers.

 

[Dryver14]

You need to format the recieving cells as you have the other cells to retain the leading zero, our formulas will not change it

 

[ayazgreat]

zicitroj use Dryver14 formula by removing +0 from end

IF(RIGHT(A1,1)="0",REPLACE(A1,5,1,1),A1)

 

[Peter_SSs]

.. or possibly this modification of Krishnakumar's formula?

=TEXT(A1+(MOD(A1,10)=0),"00000")

 

[zicitroj]

Wow! You guys are great!Thanks to all those who contributed.
Both ayaz and Peter's solutions work great!