How to Add a Zero in Front of a Number in Excel - Tutorial
Excel tutorial on how to add a zero in front of a number in Excel, or what is sometimes called "leading zeros."
Knowing how to add a 0 as the first number in Excel can be tricky. Especially if you have to figure out how to add the right number zeros in front of them.
In this Excel tutorial, we go over 3 ways to add a zero in front of a number in Excel.
1. We first add a zero in front by using an apostrophe and the zeros in each cell. This converts your numbers to text format, letting you add however many leading zeros you want.
2. We, next, insert a zero before a number by using the REPT function.
3. The third way we add leading zeros in front of cells is by using Custom Formatting.
Click here to read our step-by-step article on how to add a zero before a number in Excel: yacostasolutions.com/zero-in-...
00:00 - Intro on How to Add Zeros in Front of a Number in Excel (i.e., Leading Zeros)
00:30 - How to Add a Zero Before a Number by Using an Apostrophe
01:28 - Learn Ways to Use the REPT Function to Insert Zeros in Front of Numbers
03:40 - See How to Apply Custom Formatting to Add 0 in Front of a Number
On a related topic, make sure to watch my tutorial on the Leading Zeros Beta: • Excel Leading Zeros Se...
If you liked this, make sure to get your FREE pdf list of top Excel keyboard shortcuts: yacostasolutions.com/newsletter/
*FYI:
This tutorial was created using the desktop version of Excel in Microsoft 365. You can upgrade to this version here: yacostasolutions.com/MS365
The screen recording + editing was done using Techsmith Camtasia. You can check it out here: yacostasolutions.com/camtasia
Thanks for watching this Microsoft Excel tutorial and, don’t forget, if you like this video, please “Like” and “Share” with your friends - it really helps us out 😊
To catch our Excel tutorials as they come out, subscribe to this channel by clicking: yacostasolutions.com/youtube
Thanks!
*DISCLAIMER: This video description contains affiliate links, which means that if you click on one of the product links, I’ll receive a small commission. As an Amazon Associate I earn from qualifying purchases. This helps support the channel and allows us to continue to make tutorials like this. Thank you for your support!
#YAcostaTutorials
Пікірлер: 129
Like this tutorial? Make sure to grab your FREE pdf list of top Excel keyboard shortcuts: yacostasolutions.com/newsletter/
@feljunpaquingan17
2 жыл бұрын
thanks, i got mine maam
REPT function worked for me thankss
@YAcostaSolutions
2 жыл бұрын
That’s great! So happy this helped you 🙂
Outstanding! Thank you
@YAcostaSolutions
5 ай бұрын
Thanks, Rebecca! I’m happy you liked this 🙂
thanks,, the last one is very easy and the best
@YAcostaSolutions
Жыл бұрын
That's great, Henry! I'm happy this helped you 🙂
Thank you soo much, i just impressed my boss, you made it so easy to understand, thank you once again.
@YAcostaSolutions
Жыл бұрын
That’s awesome, Olivia! 🙌 I’m so happy this helped you so much 🙂
VERY USEFUL IT HELPED MY A LOT THANKX
@YAcostaSolutions
2 жыл бұрын
That’s great! I’m so happy this helped you 🙂 Thanks!
Finally the magic revealed
@YAcostaSolutions
2 жыл бұрын
Thanks so much! I'm happy this helped you 🙂
Your video is very helpful,keep it up
@YAcostaSolutions
Жыл бұрын
Thanks so much! I’m happy this helped you 🙂
thanks a ton. This is amazing. you solved my problem within a minute for which I was worried for more than an hour.
@YAcostaSolutions
2 жыл бұрын
Awesome! Happy this helped you 🙂
@mr.chucky575
2 жыл бұрын
@@YAcostaSolutions Thanks a lot I was at work i could not put a zero before the no for someone's id . I did not know i would solve it that fast. Thanks again
This was amazing. I needed that REPT and Length function as it gives me a number that I can use as a reference in my Index Match/ VLookup formula. Just changing the format to custom format does not bring the zero in the number. The Rept and LEN function solved my problem. Thank you so much!! well done!! Brief and to the point!
@YAcostaSolutions
4 жыл бұрын
Awesome, Fassel! I’m happy this was so helpful to you. Great job!
JazakAllaha Great Video
@YAcostaSolutions
3 жыл бұрын
Thanks, Moiz! I’m glad you liked this 🙂
I would just like to say how grateful I am that I came across your video. I have spent hours on Excel trying to figure out how to format.
@YAcostaSolutions
4 жыл бұрын
That's awesome, Caleb! I'm glad this helped you and thanks for sharing 😀
I’ve been looking everywhere for a video to help me perform this function!! Thank you sooo much!!! You helped save my grade! ❤❤❤
@YAcostaSolutions
Жыл бұрын
Thanks, Sonya! I'm so happy this helped you 🙂
I had to subscribe immediately after you solved my problem.. Thanks a lot 🙏🌹
@YAcostaSolutions
2 жыл бұрын
That’s awesome! I’m glad this helped you and happy you’ve subscribed - welcome aboard! Thanks so much 🙂
Thanks 😊
@YAcostaSolutions
Жыл бұрын
Thanks! Happy you liked this 🙂
Brilliant... The last example, so simple when you know
@YAcostaSolutions
3 жыл бұрын
Thanks so much, Barry! I think it’s a pretty cool trick too 😉 I’m happy this was helpful for you!
i am soo happy mam, Thanks a ton
@YAcostaSolutions
2 жыл бұрын
That's great! I'm happy you liked this 🙂
Excellent! You made my day I was so frustrated for this, thanks a lot!
@YAcostaSolutions
4 жыл бұрын
Thank you, Betsabe! I’m glad this helped you out 🙂
Thanks so much ...The second step was brilliant i was looking for this step 🙏🙏👍👍
@YAcostaSolutions
Жыл бұрын
Awesome, Savan! I'm happy this helped you 🙂
Nice thank you
@YAcostaSolutions
3 жыл бұрын
Thank you too!
Never knew Rept function before. Love it Thank you so much
@YAcostaSolutions
4 жыл бұрын
That's great, zac! Glad you learned something new 🙂
Thank you miss! It worked.
@YAcostaSolutions
3 жыл бұрын
Awesome! Happy this was helpful for you 🙂
I Really Thank You From The bottom of my heart... Thank You.. You solved my problem... With love from India
@YAcostaSolutions
3 жыл бұрын
You’re so very welcome 😊 Happy this helped you.
@reena6170
3 жыл бұрын
@@YAcostaSolutions Yes... This helped me sort my voter list....😊😊
Thanks ... YA, That was super useful...especially REPT() function... You made made my day! SUBSCRIBED .. hope to see more useful stuff.
@YAcostaSolutions
5 жыл бұрын
Awesome, Creativity_Inside! Happy to help and welcome aboard 🙂
Very useful 👍
@YAcostaSolutions
2 жыл бұрын
Awesome! Happy this was helpful for you 🙂
Been looking for this. Thanks!
@YAcostaSolutions
2 жыл бұрын
Awesome! Happy this helped you 🙂
Thanks alot
@YAcostaSolutions
Жыл бұрын
Thanks, Victor! I’m happy this was helpful for you 🙂
It really helps, a big thumbs up for you
@YAcostaSolutions
3 жыл бұрын
Thanks so much, Imran! I'm happy this helped you 🙂
excellent
@YAcostaSolutions
2 жыл бұрын
Thank you 🙂
Very useful video thank you.
@YAcostaSolutions
3 жыл бұрын
Thanks so much! I’m happy this was useful for you 🙂
Thanks
@YAcostaSolutions
2 жыл бұрын
You’re very welcome, Wesley. I’m happy you liked this 🙂
Thank you mam
@YAcostaSolutions
2 жыл бұрын
Thank you! Happy you liked this 🙂
tq for support
@YAcostaSolutions
Жыл бұрын
Thank you, Aditya! I’m happy this helped you 🙂
Cheers! You are a legend 🙌
@YAcostaSolutions
Жыл бұрын
Thanks so much, Dean 🙂 I’m so happy you liked this!
Thank you, I did this 1:42 and it worked as intended.
@YAcostaSolutions
3 жыл бұрын
That’s great! I’m happy this was helpful to you 🙂
Thanks for nice information.
@YAcostaSolutions
3 жыл бұрын
Thanks, Muhammad. I’m happy this helped you 🙂
@muhammadabubaker8066
3 жыл бұрын
@@YAcostaSolutions 😊
THANK YOU SO MUCH!!
@YAcostaSolutions
3 жыл бұрын
Thank you 🙂 I’m happy that this helped you.
thanks!! needed this for a homework assignment
@YAcostaSolutions
4 жыл бұрын
That's great, Rafael! I'm glad this helped you 🙂
Formatting is the easiest way 😁
@YAcostaSolutions
2 жыл бұрын
It’s a favorite for sure 🙂
Thanks so much for your clear explanation and simple way to fix all different cases, it is work well for my report
@YAcostaSolutions
2 жыл бұрын
That's excellent! So happy this helped you 🙂
Thank you ☺️
@YAcostaSolutions
3 жыл бұрын
Thank you! I’m so happy you liked this 🙂
Thank you so much
@YAcostaSolutions
3 жыл бұрын
Thanks, Chan. I'm happy you liked this 🙂
really thank you
@YAcostaSolutions
3 жыл бұрын
I’m happy this helped you 🙂 Thanks so much!
Very Nice Thanks
@YAcostaSolutions
4 жыл бұрын
Thanks, Peace! Happy you liked this 🙂
done, best trick
@YAcostaSolutions
4 жыл бұрын
Thanks so much, Girish! I'm happy this was helpful for you 🙂
Isn't it fascinating that a computer program would make it so difficult to do something so easy? How about letting me type what ever number I want in the cell and then when I click on another cell YOU DON'T CHANGE IT. Wouldn't that be amazing?
@YAcostaSolutions
2 жыл бұрын
That would be a dream come true!
Thanks you so much typing zero without error
@YAcostaSolutions
4 жыл бұрын
Thanks! Happy this helped you 🙂
@pavitraudaan5562
4 жыл бұрын
@@YAcostaSolutions 2 digit Number Wale Me To Zero Lag Raha Magar, 10 Digit wale Me Zero Nahi Lag Pa raha Hai Kyuki, Reference no 00 Double Zero Se Shuru Hota Hai Magar, Zero Tahar Nahi Raha Gayab Ho Ja raha Hau Please Reply It's Very Difficult for me
good
@YAcostaSolutions
3 жыл бұрын
Thanks so much, Ngoran! I’m glad you liked this 🙂
Lovely
@YAcostaSolutions
2 жыл бұрын
Thanks so much! I’m happy you liked this 🙂
Thank u so much. But quick question like I saved the excel sheet and closed it and open it again I lost the zeros in front of all the numbers. Tried like so many times. Please help much appreciated. 😭
done subscribe
@YAcostaSolutions
Жыл бұрын
Yes!!! Thanks so much and happy to have you join us 🙂
Will you include the formula in the comments? For some reason, when I type it out, I get the Value error.
@YAcostaSolutions
4 жыл бұрын
Hi, Mallory. Sorry about that! It was the REPT function that was used. I used it 2 ways - 1. In the first example, it was used with the "&" to combine the repeated zeros with the original values. Here's an example of this: =REPT(0,2)&A2 2. The second example used the REPT function in an IF statement so that the right number of zeros would be added to make all final values the same number of characters. An example of that is: =IF((LEN(A2)
if I want to add same digit to the last of any figure then?
Hello, Hello, If the number looks like this Q002 and I want it to be like this 0002 so I want to take Q off and add 0 what formula can I use? Thank you
@JC-yi1jq
Жыл бұрын
Yes
This was really great, the problem is that when we copy this no to another cell it loses this property
@YAcostaSolutions
Жыл бұрын
Hi and thanks for your comment. Are you maybe using one of the paste special options - like Paste Values? I haven't had this issue so just curious about what could be causing it. Thanks.
Wow
@YAcostaSolutions
Жыл бұрын
Thanks so much, Amit! Glad you liked this 🙂
Thanks! Very Nice! Today, your custom Formatting did not work. Normally It does work. What could be the reasons? There were roughly 300 records and it worked for roughly 150 but for rest, it didn't work. Thanks
@YAcostaSolutions
4 жыл бұрын
Hi, Peace. I’m sorry this didn’t work for you. I’m thinking that if this worked for half your numbers, but not the other half, that there may be a formatting issue with the ones that didn’t work. Can you go back and check them to see if they’re formatted as a number? Please let me know what you find. Thanks
@shoppersdream
4 жыл бұрын
@@YAcostaSolutions Thanks for your reply. I checked it and I noticed that all are General. None of them is formatted as a number. Half of them I was able to change using your method but the other half does not add preceding zero's. After wasting a lot of time, I tried your IF Method and that worked fine. Thanks
@YAcostaSolutions
3 жыл бұрын
Awesome! Except I’m truly sorry about the time you ended up wasting. But happy that you got it resolved.
Question: when I tried this it didn't work and I followed the exact step.
الف رحمة ع موتاج
the lesson starts at 3:45
How to combine 2 cell with zero in front of a number in excel without change their value?
@YAcostaSolutions
4 жыл бұрын
Hello, Hanif. This is an interesting question that I hadn't really thought about before! Whichever option you use to combine cell contents (ampersand, CONCAT, / CONCATENATE, or TEXTJOIN), you'll need to add, in quotation marks, the number of zeros you want in the front. For example, if you want your result to have 2 zeros in front: -Using the ampersand: ="00"&A1&B1 -Using CONCAT or CONCATENATE: CONCATENATE("00",A1,B1) In case you are interested, here is a link to a tutorial I did on using the ways I mentioned above to combine cell contents: kzread.info/dash/bejne/hWirqLyGnq21frg.html Please let me know if you have more questions. Thanks!
@amrifath
4 жыл бұрын
@@YAcostaSolutions Thanks a bunch for your attention. In my case there are 2 number which are "00240" and "03418" i want to combine theme into "00240 03418" in one cell. Should it possible in excel?
@YAcostaSolutions
3 жыл бұрын
Hi, Hanif. I’m so sorry I didn’t respond sooner. I’m only just now seeing your response. Yes, you’re able to combine the values, but it’s a bit tricky. The easiest option, in my opinion, would be to use the TEXT function to apply custom formatting to each cell via the formula and combine them this way. It would look something like this: =TEXT(A1,"00000")&" "&TEXT(B1,"00000") *The quotation marks can be a bit touchy. For example, mine only work when I type them on my laptop. The ones on my phone are formatted slightly different and don’t work. So, if you copy and paste this and it doesn’t work, retype your quotation marks when in Excel. Here’s a link to my tutorial on the TEXT function if you’d like to understand it a little better: kzread.info/dash/bejne/oKqlqM-PesS0ctI.html You can also use CONCAT or CONCATENATE, but I think that’s harder for this because you have to put more thought into how to format each cell that you’re combining. But, if you use either of those, the formula would look like: =CONCATENATE("00",A1," ","0",B1) - where A1 has the cell with 2 zeros in front and B1 has the cell with 1 zero in front. I hope this helps (and that it's not too late to be helpful). Please let me know if you have more questions. Thanks
@amrifath
3 жыл бұрын
@@YAcostaSolutions Thanks for your help. I can combine them with text command.
@YAcostaSolutions
3 жыл бұрын
Awesome! Happy it worked for you 🙂
WE DOSE NOT USE FORMULA ON ZERO VALUE DIGIT WITH