Converting number to have leading zeros and punctuation mark

%3CLINGO-SUB%20id%3D%22lingo-sub-166927%22%20slang%3D%22en-US%22%3EConverting%20number%20to%20have%20leading%20zeros%20and%20punctuation%20mark%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-166927%22%20slang%3D%22en-US%22%3E%3CP%3ETrying%20to%20convert%203%20to%20'0003%2C%20but%20where%20the%20result%20does%20not%20show%20the%20'%20instead%20we%20want%20it%20to%20just%20show%200003%20with%20the%20green%20triangle%20in%20the%20corner.%20Have%20tried%20numerous%20formats%20and%20functions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-166927%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1724856%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20number%20to%20have%20leading%20zeros%20and%20punctuation%20mark%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1724856%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20works%20perfectly!%26nbsp%3B%20However%2C%20for%20clarification%2C%20when%20creating%20the%20Custom%20Format%2C%20the%20number%20of%200's%20input%20into%20the%20format%20will%20be%20the%20number%20displayed.%26nbsp%3B%20However%2C%20I%20found%20that%20if%20you%20have%20a%20six-digit%20number%20to%20display%20(e.g.%20000245)%2C%20the%20Custom%20Format%20code%20needs%20to%20be%20%22000%23%23%23%22%20-%20including%20one%20%23%20for%20each%20non-zero%20number%20you%20wish%20to%20display.%26nbsp%3B%20Interestingly%2C%20if%20you%20only%20enter%20%22000%23%23%22%20as%20the%20format%20code%20for%20your%20six-digit%20number%20with%20three%20leading%20zeros%2C%20the%20result%20will%20be%20%2200245%22%20-%20it%20will%20truncate%20the%20displayed%20number%20starting%20at%20the%20left%20-%20overwriting%20the%20desire%20to%20display%20three%20leading%20zeros...%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-167660%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20number%20to%20have%20leading%20zeros%20and%20punctuation%20mark%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-167660%22%20slang%3D%22en-US%22%3EHi%20Bill%3CBR%20%2F%3E%3CBR%20%2F%3EWithout%20seeing%20the%20original%20file%20in%20action%2C%20I%20don't%20have%20any%20ideas%20on%20how%20to%20get%20your%20document%20working%20for%20you.%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20may%20also%20be%20worth%20reaching%20out%20to%20the%20now%20retired%20individual%20to%20see%20if%20they%20are%20happy%20to%20remind%20your%20colleague%20on%20how%20that%20worked%20(professional%20courtesy%20and%20what%20not).%3CBR%20%2F%3E%3CBR%20%2F%3ESorry%20I%20don't%20have%20a%20better%20answer%20for%20you.%3CBR%20%2F%3E%3CBR%20%2F%3EBest%20wishes%20with%20the%20spreadsheet.%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%3CBR%20%2F%3EDamien%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-167245%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20number%20to%20have%20leading%20zeros%20and%20punctuation%20mark%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-167245%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Bill%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20don't%20need%20a%20text%20but%20number%20representing%20with%20leading%20zeroes.%20If%20so%20you%20may%20just%20apply%20custom%20format%20like%3C%2FP%3E%0A%3CPRE%3E000%23%3C%2FPRE%3E%0A%3CP%3Eto%20all%20your%20few%20hundred%20cells.%20The%20result%20will%20be%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%2099px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F29552i57A26455BFCB83CA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-167204%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20number%20to%20have%20leading%20zeros%20and%20punctuation%20mark%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-167204%22%20slang%3D%22en-US%22%3E%3CP%3ETrying%20to%20assist%20a%20colleague%20with%20this.%20He%20said%20that%20the%20person%20that%20trained%20him%20on%20his%20spreadsheet%20showed%20him%20a%20way%20to%20make%20it%20come%20out%20the%20way%20we%20want%2C%20but%20he%20did%20not%20take%20notes.%20The%20other%20person%20retired%20over%20a%20year%20ago%20so%20we%20don't%20know%20what%20she%20had%20done.%20Essentially%20we%20are%20attempting%20to%20get%20the%20same%20result%20as%20when%20you%20physically%20type%20'0003%20into%20the%20cell%20(which%20gives%20you%200003%20with%20the%20little%20warning%20message%20that%20a%20number%20is%20stored%20as%20text)%20from%20an%20initial%20value%20of%203.%20Seems%20odd%2C%20but%20the%20apostrophe%20has%20to%20be%20there%20due%20to%20how%20the%20data%20pulls%20from%20the%20sheet%20to%20where%20it%20gets%20downloaded.%20And%20we%20are%20trying%20to%20find%20an%20easier%20way%20than%20manually%20updating%20500%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-166948%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20number%20to%20have%20leading%20zeros%20and%20punctuation%20mark%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-166948%22%20slang%3D%22en-US%22%3EHi%20Bill%3CBR%20%2F%3E%3CBR%20%2F%3ENot%20sure%20I%20follow%20the%20logic%20although%20it%20could%20be%20just%20my%20understanding.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%203%20is%20converted%20to%20'0003%2C%20then%20I%20am%20not%20sure%20how%20it%20will%20not%20show%20the%20'%20if%20the%20answer%20will%20always%20have%20it%3F%20When%20would%20the%20result%20not%20have%20the%20'%3F%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%3CBR%20%2F%3EDamien%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1724983%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20number%20to%20have%20leading%20zeros%20and%20punctuation%20mark%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1724983%22%20slang%3D%22en-US%22%3EIf%20I%20may%2C%20you%20don't%20need%20a%20%23%20sign%20for%20each%20non-zero%20number.%20It%20just%20means%20display%20a%20number%20if%20there%20is%20one%20and%20don't%20display%20one%20if%20there%20is%20not%20a%20number%20in%20that%20place.%3CBR%20%2F%3E%3CBR%20%2F%3EA%20zero%20means%20display%20a%20zero%20if%20there%20is%20not%20a%20number%20in%20that%20place.%20So%2000000%23%20will%20show%20245%20as%20000245.%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20could%20also%20use%20000000%2C%20but%20there's%20no%20difference%20if%20your%20data%20is%20integers%20as%20you'll%20always%20have%20at%20least%20one%20integer.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1726335%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20number%20to%20have%20leading%20zeros%20and%20punctuation%20mark%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1726335%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F813473%22%20target%3D%22_blank%22%3E%40scottmccanna%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fcustom-number-formats%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EExcel%20custom%20number%20formats%3C%2FA%3E%26nbsp%3Bwhich%20explains%20how%20custom%20number%20format%20works.%20Lot%20of%20other%20info%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Trying to convert 3 to '0003, but where the result does not show the ' instead we want it to just show 0003 with the green triangle in the corner. Have tried numerous formats and functions.

7 Replies
Highlighted
Hi Bill

Not sure I follow the logic although it could be just my understanding.

If 3 is converted to '0003, then I am not sure how it will not show the ' if the answer will always have it? When would the result not have the '?

Cheers
Damien
Highlighted

Trying to assist a colleague with this. He said that the person that trained him on his spreadsheet showed him a way to make it come out the way we want, but he did not take notes. The other person retired over a year ago so we don't know what she had done. Essentially we are attempting to get the same result as when you physically type '0003 into the cell (which gives you 0003 with the little warning message that a number is stored as text) from an initial value of 3. Seems odd, but the apostrophe has to be there due to how the data pulls from the sheet to where it gets downloaded. And we are trying to find an easier way than manually updating 500 cells.

Highlighted

Hi Bill,

 

Perhaps you don't need a text but number representing with leading zeroes. If so you may just apply custom format like

000#

to all your few hundred cells. The result will be like

image.png

 

 

Highlighted
Hi Bill

Without seeing the original file in action, I don't have any ideas on how to get your document working for you.

It may also be worth reaching out to the now retired individual to see if they are happy to remind your colleague on how that worked (professional courtesy and what not).

Sorry I don't have a better answer for you.

Best wishes with the spreadsheet.

Cheers
Damien
Highlighted

@Sergei Baklan 

This works perfectly!  However, for clarification, when creating the Custom Format, the number of 0's input into the format will be the number displayed.  However, I found that if you have a six-digit number to display (e.g. 000245), the Custom Format code needs to be "000###" - including one # for each non-zero number you wish to display.  Interestingly, if you only enter "000##" as the format code for your six-digit number with three leading zeros, the result will be "00245" - it will truncate the displayed number starting at the left - overwriting the desire to display three leading zeros... 

Highlighted
If I may, you don't need a # sign for each non-zero number. It just means display a number if there is one and don't display one if there is not a number in that place.

A zero means display a zero if there is not a number in that place. So 00000# will show 245 as 000245.

You could also use 000000, but there's no difference if your data is integers as you'll always have at least one integer.
Highlighted

@scottmccanna 

Please check Excel custom number formats which explains how custom number format works. Lot of other info as well.