 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008adding a leading 00 to some loan numbers
I've got several thousand loan numbers which need to be updated to a ten-digit format by adding a leading 00. That's easy enough to do, HOWEVER, some of them have already been updated with the leading 00. What formula can I use to add 00 only to those which have not already been updated? Thanks. -- alan =right(rept("0",10)&a1,10) If the values are all numeric (not text), you could apply a custom format: Select the range format|cells|Number tab|Custom category 0000000000 or use a formula like this if the cell looks numeric: =text(a1,rept("0",10)) or =text(a1,"0000000000") > I've got several thousand loan numbers which need to be updated to a > ten-digit format by adding a leading 00. [quoted text clipped - 5 lines] > -- > alan  SignatureDave Peterson Thank you so much ---- the numbers were all text, and your formula worked perfectly. I see also that it will work perfectly for some 7-digit numbers that have be updated with 000, and in fact (by changing the number after the comma)will work for addition of as many zeroes as will be necessary to expand to any digit matrix. I'd been totally unaware of the "rept" factor. Thanks again -- alan > =right(rept("0",10)&a1,10) > [quoted text clipped - 19 lines] >> -- >> alan A good book on Excel, such as JohnWalkenbach's Bible series would help you. It's only $40 and easy reading Tyro > I've got several thousand loan numbers which need to be updated to a > ten-digit format by adding a leading 00. [quoted text clipped - 5 lines] > -- > alan Thanks for the suggestion --- I'll check it out. -- alan >A good book on Excel, such as JohnWalkenbach's Bible series would help you. >It's only $40 and easy reading [quoted text clipped - 10 lines] >> -- >> alan
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.