Replace ending zeroes in a list of numbers by 1

zicitroj

New Member
Joined
Mar 22, 2011
Messages
6
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

zicitroj

New Member
Joined
Mar 22, 2011
Messages
6
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

Well-known Member
Joined
Mar 22, 2010
Messages
2,396

ADVERTISEMENT

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

Well-known Member
Joined
Jan 19, 2008
Messages
1,151
zicitroj use Dryver14 formula by removing +0 from end

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
.. or possibly this modification of Krishnakumar's formula?

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

zicitroj

New Member
Joined
Mar 22, 2011
Messages
6
Wow! You guys are great!Thanks to all those who contributed.
Both ayaz and Peter's solutions work great!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,142
Messages
5,599,971
Members
414,353
Latest member
ljhan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top