I have a particular code file that has six numbers, but two are in front of the decimal point, and four are behind it. It's supposed to be shown "fully packed" so all leading and trailing zeroes are expected to display. I just use the same trick, but use custom formatting 00.0000 to show all positions of the number. Not trying to get too specific, but pointing out for others that the custom format can also include decimal points if your data requires it.
I've been using excel for over 20 years and I didn't figure this out. - my work around was normally format and switch to text. Oh well. Thanks for the videos Leila - they've been very helpful.
@@LeilaGharani I need help. I accidentally concatenated data without any formulas or conditional formatting. I don't know how this has happened. It is as if it autofilled but I didn't even auto fill either. Please explain if and when possible.
@@LeilaGharani mine is a pattern within strings so the zero needs to in the right place, does it make sense that every zero that is being eliminated from my data is a lead one?
Thank you Leila , but i think please consider on your next video how to make a CSV format excel working keep the leading zeros without them dissapearing everytime you save assuming not all of the digits are of the same legnth.
Technically speaking they do not disappear: Once you add your leading zeros, save as csv, the zeros are still there and can be confirmed opening the file in something like text editor. Why you don't see this is because when you open the csv in excel it automatically converts it to general cell type and as a results drops the zeros again.
You told everyone my secret!! No one at my job knows how to keep the leading zeros and now the cats out of the bag lol. Thank you for all of your learning lessons in excel 😁😁
Formatting the column as a text works better for me. If I do custom formatting the value doesn’t show the 0’s which is an issue of doing a Vlookup with that cell.
Great, except the part numbers are not all the same length. Other than formatting cells as text, there needs to be a way to keep leading zeros for varying length numbers. How about a nice checkbox that says keep leading zeros?
Leila’s tip does not format the numbers as text. It creates a “mask” to view variable length numeric values and formula results using a uniform length, and preserves the values in the cells for computation purposes, etc. Excel is designed primarily for number crunching. Dropping insignificant leading and trailing zeros past the decimal keeps the math easier to understand in the formula bar. Keeping leading/trailing zeros upon entry can be simulated by formatting the cell(s) to the desired length prior to entry of the numeric values or formulas.
In my experience ... If the ID (people, products, companies) requires leading zeros, it should be treated as text (in Excel and database). The ID does not have a "counter" function. Otherwise, it is not possible to verify the length of the filled ID. In any case, it is necessary to be careful that the format must be the same in the source and destination, for example in the VLOOKUP function.
For any reference function. We have over 1000 stores so for the 1-9 we use 0001 for example, or store 300 is 0300. We need this treated as a number and not text. Just one simple example where we need it as numbers since we are often referencing assuming it is a number and many people aren't skilled enough to troubleshoot and see, "ohhh, this cell is formatted as a text," and especially not when some dummies change format to a text on a table so you click the column and format will say number when in actuality some dumb dumb changed only the cells under the header and that makes it _REALLY_ fun 🙄 how about we all just learn the proper ways instead of alternates that get you what you're looking for in that one moment and never having the foresight of if your data needs to be manipulated by someone other than yourself? Shit drives me up a WALL and when I see shit break like that I just copy all the data immediately and paste special values with format in a fresh sheet to get away from such asinine nonsense wasting my time.
You can also format it to text - I wonder why excel is not a bit smarter about entering 0 prefix, which is often a sign it’s not supposed to treat it as number
As per the end of what she said, you can now still treat the content within the cell as a number: If you were, for example, to use a lookup where your reference range are all numbers, formatted as a number, it will not return any matches due to formatting as text. Furthermore, if you're going to change the cell format, why not just do it proper and keep it a number?
I used this, copied and pasted as text, NO GOOD! 04 became 4 because excel still 'sees' it as a number. Could not convert to 04 to export to a database because database would still see a 4. Example solution: "04" is in A1: Formula - =TEXT(A1,"00") This gave me 04, then I could copy and paste as text. Leila you are fantastic, thank you for your videos, they make my days so much easier!! PS. I still needed to use this before using that formula!
Unfortunately it is just a formatting, which means it gets lost in formulas (e.g. combining with text to create strings). I need this often and I already knew this way. Is there a similar easy solution to add /keep the zeros and actually have this in the cells value (as text is fine)?
@@a.rahimali4022 Thanks a lot.I still don't like using formatting tools for containing value information, but this is the best tip I got about this yet.
If you have no intention of using a number for calculations and want to add leading or trailing zeros, use an apostrophe at the left upon entry. The result is treated as text by Excel.
@@c17nav Thanks. I already know about this solution, but it does not work when I get a new dataset and ECXEL removed all the zeros initially. It ist not handy to get the zeros back.
Is there a way for that formatting to survive in a formula referencing that cell? I frequently need to concatenate information like that but get stuck using the bad formatting you are trying to help us get away from.
Do not mistake mathematical operations (addition, multiplication, etc.) that calculate numeric values with string operations like concatenation and parsing that produce text. Suppose you have the number 20 in cell B3 (or a formula that evaluates to 20), and in cell C3 you want to show “part xyz0020” based on the contents B3. The formula for cell C3 would be =“part xyz”&TEXT(B3,”0000”). Note that use of quotation marks and the ampersand are required to produce a successful string formula that is not a number.
This is great, I use it when I can, my issue is some of my file numbers are 8,9 or even 11 digits long all with 1 or two leading zeros and need to be exact. 🤷🏻♀️
What if you have a long list of numbers that you want to keep the leading zeros for, but those numbers all have different lengths? They all start with zero but some are 3 digits long, some are 5, and some are 8... What can I do here to make sure that it keeps all of the leading zeros but it also keeps the correct number of characters? I hope that made sense
THANK YOU!! I keep trying to change the formatting to text, but Excel is "Helpful" and reverts them again. Oops. my number are Hexadecimal. I had to format them as text and do the ' trick to keep the zeros from dissapearing.
@@PatrickRatnaraja Are the leading zeroes from text? This video is displaying the leading zeroes via Excel display formatting, rather than actually putting the leading zeros present. But at least we know now that it won't work. Thank you for testing.
@@toddkes5890 yes I understand. It was not a criticism. It was a question on how to manage this in a csv file as that is a requirement for me. I pay for Leila’s courses and enjoy her videos. So definitely not a criticism.
You might need a couple extra columns to format the numbers correctly, but how about trying "=TEXT(A4,"0000")", but instead of the '0000' you use the RPT command taking the number of zeros from a hidden column. The problem with this is it will convert the number to text, so you can't use it for mathematics.
Text-to-columns!!!! Write “xx” in the first cell of the column. Mark the whole column and use text-to-columns and change the column to text (on the last screen on the wizard). After that everything you type is text. Trust me, this is the best way. ;) Good luck!
In some cases, this is a very bad guide. In a situation where, for example, you need to search for numbers starting with 0. In this case, you must format cells as text. Then also leading zeroes doesn't disappear.
@@iji_k It is. It all depends on if you are at the end of your calculations, what you are using it for and which column is for you to look at and which is for your boss 😃👍
I mean, you could just highlight the column and drop the type to text quicker than your formula even, but either way then you can't use it as a reference or lookup against numbers...
Nice, clear and concise delivery. Thank you.
I have a particular code file that has six numbers, but two are in front of the decimal point, and four are behind it. It's supposed to be shown "fully packed" so all leading and trailing zeroes are expected to display. I just use the same trick, but use custom formatting 00.0000 to show all positions of the number. Not trying to get too specific, but pointing out for others that the custom format can also include decimal points if your data requires it.
Leyla, you really are a Godsend. I needed to know this so many times… thank you.
Because of you, the world is just much better and efficient now!!!!!
You are my favorite Excel tutor! I really appreciate your Excel skills and your amazing teaching skills!
Thank you! Thank you! I'm an experienced Excel user and I did not know that trick. Very useful to me. Thank you!
Loved the short video! some times I simply don't have 15-20 min. to watch a video, keep it coming Leila
You are absolutely fantastic teacher
I was familiar with custom formatting, but did not know about Ctrl+1 as a shortcut. Cool, thank you!
I love you ! Every time I hit the rock on some stupid as hell option, you are there to make my day easier ! Cheers :) !
I've been using excel for over 20 years and I didn't figure this out. - my work around was normally format and switch to text. Oh well. Thanks for the videos Leila - they've been very helpful.
Glad it helped!
@@LeilaGharani I need help. I accidentally concatenated data without any formulas or conditional formatting. I don't know how this has happened. It is as if it autofilled but I didn't even auto fill either. Please explain if and when possible.
@leila it did worked but when I save and open the file again it comes back to same thing
@@LeilaGharani mine is a pattern within strings so the zero needs to in the right place, does it make sense that every zero that is being eliminated from my data is a lead one?
Great tip! Thanks Leila. Thumbs up!!
Thanks for watching!
Brilliant tip
Thanks a lot, Ms/Leila
So easy to fix, it is not even fair. Thank you.
All these years and I didn't know that. Thanks At least I might be able to use them before I retire in 3 months.
Thanks. For just a couple of numbers what I do works( '0020). But for several numbers or an existing list to paste this is great!
OMG I love this! Thank you!
I'm glad you like it, Melissa.
Thank you!
Always excellent tips!
This is so satisfying to see
Waoh so tricky, simple. Thanx
Very good
Thank 🌷
Omg 😲this is brilliant. I've been changing the formatting to text to keep the zeros. This is awesome 👍
I mean, your method works fine until you try and use it as a reference against other numbers
Thank you Leila , but i think please consider on your next video how to make a CSV format excel working keep the leading zeros without them dissapearing everytime you save assuming not all of the digits are of the same legnth.
Technically speaking they do not disappear: Once you add your leading zeros, save as csv, the zeros are still there and can be confirmed opening the file in something like text editor. Why you don't see this is because when you open the csv in excel it automatically converts it to general cell type and as a results drops the zeros again.
Awesome videos
Thanks Leila! I need to brush up on my custom number formatting skills!
Great Leila. Awesome as usual
Thank you for dropping by.
Thanks 😊👍🏼
Thanks Ma'am
Your tips are great and I'm really loving the shorts. Thank you!
Finally thankyou 😊
I just needed this the other day. Thanks!
Old but fresh.🤗
Great, thank you!!
Thanks Leila. I need this for a serial code that begins with 0.
I love you. Like seriously...
this is useful to know! thanks Leila! xD
You told everyone my secret!! No one at my job knows how to keep the leading zeros and now the cats out of the bag lol. Thank you for all of your learning lessons in excel 😁😁
Haha Same 😁 using that trick for almost 6 years to get 8 digits 🤷
Ok am a jasiaiqaooao
You're cool, if you know such secrets.😁
Life saving
Oh yes. Great tip.
thanks
Thank U!!!
Formatting the column as a text works better for me. If I do custom formatting the value doesn’t show the 0’s which is an issue of doing a Vlookup with that cell.
This is much cleaner than what I've been doing which is:
'0
Lmfao
Great, except the part numbers are not all the same length. Other than formatting cells as text, there needs to be a way to keep leading zeros for varying length numbers. How about a nice checkbox that says keep leading zeros?
Leila’s tip does not format the numbers as text. It creates a “mask” to view variable length numeric values and formula results using a uniform length, and preserves the values in the cells for computation purposes, etc. Excel is designed primarily for number crunching. Dropping insignificant leading and trailing zeros past the decimal keeps the math easier to understand in the formula bar. Keeping leading/trailing zeros upon entry can be simulated by formatting the cell(s) to the desired length prior to entry of the numeric values or formulas.
I love you ❤ dear my genius!😘
Thx so much ive nearly gave up
In my experience ... If the ID (people, products, companies) requires leading zeros, it should be treated as text (in Excel and database). The ID does not have a "counter" function. Otherwise, it is not possible to verify the length of the filled ID. In any case, it is necessary to be careful that the format must be the same in the source and destination, for example in the VLOOKUP function.
For any reference function. We have over 1000 stores so for the 1-9 we use 0001 for example, or store 300 is 0300. We need this treated as a number and not text. Just one simple example where we need it as numbers since we are often referencing assuming it is a number and many people aren't skilled enough to troubleshoot and see, "ohhh, this cell is formatted as a text," and especially not when some dummies change format to a text on a table so you click the column and format will say number when in actuality some dumb dumb changed only the cells under the header and that makes it _REALLY_ fun 🙄 how about we all just learn the proper ways instead of alternates that get you what you're looking for in that one moment and never having the foresight of if your data needs to be manipulated by someone other than yourself? Shit drives me up a WALL and when I see shit break like that I just copy all the data immediately and paste special values with format in a fresh sheet to get away from such asinine nonsense wasting my time.
You can also format it to text - I wonder why excel is not a bit smarter about entering 0 prefix, which is often a sign it’s not supposed to treat it as number
As per the end of what she said, you can now still treat the content within the cell as a number: If you were, for example, to use a lookup where your reference range are all numbers, formatted as a number, it will not return any matches due to formatting as text. Furthermore, if you're going to change the cell format, why not just do it proper and keep it a number?
Or insert ' before the number
I used this, copied and pasted as text, NO GOOD! 04 became 4 because excel still 'sees' it as a number. Could not convert to 04 to export to a database because database would still see a 4.
Example solution: "04" is in A1: Formula - =TEXT(A1,"00")
This gave me 04, then I could copy and paste as text.
Leila you are fantastic, thank you for your videos, they make my days so much easier!!
PS. I still needed to use this before using that formula!
Great
Do you have a video which explains about entering time format directly entering number like 1400 but it should appear with colon mark Like 14:00
Thank you! I've been annoyed by this for too long
What about when I want to concatenate the formatted number with another cell? How do I keep the four digits?
Thanks, Now if I could only get it to keep the date order that I select.
Unfortunately it is just a formatting, which means it gets lost in formulas (e.g. combining with text to create strings). I need this often and I already knew this way.
Is there a similar easy solution to add /keep the zeros and actually have this in the cells value (as text is fine)?
When combining with text, you can use "=Text" function to keep the leading zeroes. Example "=CONCATENATE(A1,TEXT(B1,"0000"))". Hope I helped.
@@a.rahimali4022 Thanks a lot.I still don't like using formatting tools for containing value information, but this is the best tip I got about this yet.
If you have no intention of using a number for calculations and want to add leading or trailing zeros, use an apostrophe at the left upon entry. The result is treated as text by Excel.
@@c17nav Thanks. I already know about this solution, but it does not work when I get a new dataset and ECXEL removed all the zeros initially. It ist not handy to get the zeros back.
Is there a way for that formatting to survive in a formula referencing that cell? I frequently need to concatenate information like that but get stuck using the bad formatting you are trying to help us get away from.
Do not mistake mathematical operations (addition, multiplication, etc.) that calculate numeric values with string operations like concatenation and parsing that produce text. Suppose you have the number 20 in cell B3 (or a formula that evaluates to 20), and in cell C3 you want to show “part xyz0020” based on the contents B3. The formula for cell C3 would be =“part xyz”&TEXT(B3,”0000”). Note that use of quotation marks and the ampersand are required to produce a successful string formula that is not a number.
This is great, I use it when I can, my issue is some of my file numbers are 8,9 or even 11 digits long all with 1 or two leading zeros and need to be exact. 🤷🏻♀️
Are they codes like item codes, postcodes, phone numbers (ie not real numbers). If so, format the column as text instead of number.
Interesting, but imho the best way to do that is by TEXT formula
What if you have a long list of numbers that you want to keep the leading zeros for, but those numbers all have different lengths?
They all start with zero but some are 3 digits long, some are 5, and some are 8... What can I do here to make sure that it keeps all of the leading zeros but it also keeps the correct number of characters?
I hope that made sense
👌
THANK YOU!! I keep trying to change the formatting to text, but Excel is "Helpful" and reverts them again. Oops. my number are Hexadecimal. I had to format them as text and do the ' trick to keep the zeros from dissapearing.
Can u help me with leading Zeros but length varies like 2, 3 upto 10 digits
Another way is starting with a leading ( ' ) apostrophe. It will disappear while the zero remains apparent
So if I had a file I told you to lookup against, a1 is 0020 and in your file you have '0020, you're not going to get a match.
💖
Hi Will this work in a CSV file?
Try it, find out, and let us know?
Sorry CSV file
@@toddkes5890 It loses the leading zeros when the CSV file is open. Leading zeros are normally found in exported CSV files
@@PatrickRatnaraja Are the leading zeroes from text? This video is displaying the leading zeroes via Excel display formatting, rather than actually putting the leading zeros present.
But at least we know now that it won't work. Thank you for testing.
@@toddkes5890 yes I understand. It was not a criticism. It was a question on how to manage this in a csv file as that is a requirement for me. I pay for Leila’s courses and enjoy her videos. So definitely not a criticism.
Thanks! What if I want it to just listen to what I type! I mean, maybe I don't want 4 digits in every row
Try formatting it as text.
You might need a couple extra columns to format the numbers correctly, but how about trying "=TEXT(A4,"0000")", but instead of the '0000' you use the RPT command taking the number of zeros from a hidden column. The problem with this is it will convert the number to text, so you can't use it for mathematics.
Text-to-columns!!!! Write “xx” in the first cell of the column. Mark the whole column and use text-to-columns and change the column to text (on the last screen on the wizard).
After that everything you type is text. Trust me, this is the best way. ;)
Good luck!
My way was conerting to text, too. But your referencing + formula gave me the leftover.
But if you use this cell to xlookup value from another celle it's not gonna work right ?
So long as your other cells are also numbers, yes it will.
Not Working for HexaDecimal sysyem I want F(Hex) to be display as 0x000F Thank you
The problem is that is not the actual value.
Ya ampun ❤️
I thought we had to make them string all this time 🤯
How to add zero after a single digit?
It will not work if you concat with other number
In some cases, this is a very bad guide. In a situation where, for example, you need to search for numbers starting with 0. In this case, you must format cells as text. Then also leading zeroes doesn't disappear.
Or just change it to text. . .
I'm did this 7 years ago ready...
That's cool, I use =TEXT(A4,"0000")
Great! Thank for sharing.
That’s making it a text
If you know what you’re doing, then no problem.
@@iji_k It is. It all depends on if you are at the end of your calculations, what you are using it for and which column is for you to look at and which is for your boss 😃👍
I mean, you could just highlight the column and drop the type to text quicker than your formula even, but either way then you can't use it as a reference or lookup against numbers...