Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-26-2021, 01:38 AM
RAINBOWONFIRE RAINBOWONFIRE is offline adding zeros before a number depending on the number of digits Windows 10 adding zeros before a number depending on the number of digits Office 2019
Novice
adding zeros before a number depending on the number of digits
 
Join Date: Nov 2021
Posts: 3
RAINBOWONFIRE is on a distinguished road
Default adding zeros before a number depending on the number of digits

hello


i'm ron from ISRAEL and i have a question as a beginner.


i've a list of costumers. with about 5 columns.


the problematic column is the costumer number column.


at the beginning of each customer number there should be two or three zeros.


for example if it is 345 it should have two zeroes before the 345.
if the number is 3456 it should have three zeros before the 3456.


is it complicated to add the zeros to all 500 customers?


thank you
love
Reply With Quote
  #2  
Old 11-26-2021, 07:30 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline adding zeros before a number depending on the number of digits Windows 7 64bit adding zeros before a number depending on the number of digits Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,524
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Select the customer column
Right Click - Format Cells - Number Tab -Select Custom
In the box under "Type" enter 00000 ( five zeroes) - ok
Reply With Quote
  #3  
Old 11-26-2021, 07:49 AM
RAINBOWONFIRE RAINBOWONFIRE is offline adding zeros before a number depending on the number of digits Windows 10 adding zeros before a number depending on the number of digits Office 2019
Novice
adding zeros before a number depending on the number of digits
 
Join Date: Nov 2021
Posts: 3
RAINBOWONFIRE is on a distinguished road
Default

i will try soon.....


but i want clarify... it should distinguish between a costumer number of 3 digits and a costumer number of 4 digits and add the zeroes accordingly.....


thanks
Reply With Quote
  #4  
Old 11-26-2021, 09:08 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline adding zeros before a number depending on the number of digits Windows 7 64bit adding zeros before a number depending on the number of digits Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,524
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Sorry I misunderstood. Perhaps
Code:
=if(LEN(A1)=3,TEXT(A1,"00000"),TEXT(A1,"0000000"))
.
This only changes the formatting, the underlying value will not change
If you need to use the obtained string then
Code:
=IF(LEN(A1)=3,"00"&A1,"000"&A1)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #5  
Old 11-26-2021, 09:49 AM
RAINBOWONFIRE RAINBOWONFIRE is offline adding zeros before a number depending on the number of digits Windows 10 adding zeros before a number depending on the number of digits Office 2019
Novice
adding zeros before a number depending on the number of digits
 
Join Date: Nov 2021
Posts: 3
RAINBOWONFIRE is on a distinguished road
Default

thank you
i will try it very soon when i get to the computer..
Reply With Quote
  #6  
Old 12-03-2021, 11:06 PM
Purfleet Purfleet is offline adding zeros before a number depending on the number of digits Windows 10 adding zeros before a number depending on the number of digits Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 339
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

I have had to do this for the last 20 years on SEDOL codes and this works perfectly

=REPT("0",5-LEN(J16))&J16

Probably my fav formula!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Remove trailing zeros from number format but only for a zero value Matt C Excel 3 11-22-2021 06:46 AM
adding zeros before a number depending on the number of digits Merge field return last 4 digits of a number macquhele Mail Merge 6 08-01-2020 11:04 PM
Entering a number of 19 digits Jo Freeman Excel 11 11-08-2017 06:20 AM
Having number's digits together mohsen.amiri Word 0 06-23-2017 01:20 AM
adding zeros before a number depending on the number of digits How to create a table with a number of line depending a number entered by user Cellendhyll Word Tables 3 07-10-2014 05:49 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:09 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft