Go Back

Pad week numbers with zeros

Read time: 20 minutes

Figure 1. Pad Week Numbers With Zeros in Excel.

If we want to pad week numbers with zeros in Excel, we have to make use of the TEXT function.

Generic Formula

=TEXT(WEEKNUM(date,type),"00")

With the Excel TEXT function, we can “pad” any number value with as many zeros as we like.

How to use the TEXT Function in Excel.

We are now going to demonstrate the use of the TEXT Function using 3 simple steps.

  1. Collect the calendar dates to be padded and their corresponding week numbers and arrange within properly labeled columns of our worksheet.

Be sure to provide another labeled column with empty cells for Excel to return the padded number values.

See example illustrated below;

Figure 2. Pad Week Numbers With Zeros in Excel.

  1. In our worksheet example above, the TEXT Function we are going to enter into cell C2 is as follows;

=TEXT(WEEKNUM(A2,21),"00")

Figure 3. Pad Week Numbers With Zeros in Excel.

Excel returned “07” as the padded result for the date in cell A2.

  1. Copy the modified version of the TEXT Formula in cell C2 down into the other cells in the PADDED column.

Figure 4. Pad Week Numbers With Zeros in Excel.

The TEXT Function in Excel allows us to apply any kind of number formats, including percentage, currency, date, etc.

We can “pad” numbers with any amount of zeros we would like by applying number formats such as, “00”, “000”, “0000”.

Figure 5. Final Result.

Instant Connection to an Expert through our Excelchat Service:

Our live Excelchat Service is here for you. We have Excel Experts available 24/7 to answer any Excel questions you may have. Guaranteed connection within 30 seconds and a customized solution for you within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

can I pad numbers with leading zeros, so they are all the same length?
Solved by V. D. in 27 mins
Trying to save a csv file with custom numbers. I have 9 digit numbers, some of which start with zeros. Excel is auto deleting the zeros in the beginning. So, I format cells, custom and type in 9 zeros. The numbers are fixed. I save it and open it back up and they numbers that start with zero are back to the way they started. If I save as a workbook, this does not happen.
Solved by F. L. in 24 mins
working in a large spreadsheet. Some fields contain numbers and some are blank. I need to add zeros to the fields that are blank. Is there a way to fill all empty fields with zeros quickly?
Solved by M. F. in 12 mins

Leave a Comment

avatar