How to add leading zeros?

excel vba add leading zeros to number
how to add leading zeros in access
keep leading zeros in excel concatenate
how do you add zeros in front of a number in excel?
how to add trailing zeros in excel
remove leading zeros in excel
how to add leading zeros in google sheets
add leading zeros in excel concatenate

I have a set of data which looks something like this:

anim <- c(25499,25500,25501,25502,25503,25504)
sex  <- c(1,2,2,1,2,1)
wt   <- c(0.8,1.2,1.0,2.0,1.8,1.4)
data <- data.frame(anim,sex,wt)

   anim sex  wt anim2
1 25499   1 0.8     2
2 25500   2 1.2     2
3 25501   2 1.0     2
4 25502   1 2.0     2
5 25503   2 1.8     2
6 25504   1 1.4     2

I would like a zero to be added before each animal id:

   anim sex  wt anim2
1 025499   1 0.8     2
2 025500   2 1.2     2
3 025501   2 1.0     2
4 025502   1 2.0     2
5 025503   2 1.8     2
6 025504   1 1.4     2

And for interest sake, what if I need to add two or three zeros before the animal id's?

The short version: use formatC or sprintf.

The longer version:

There are several functions available for formatting numbers, including adding leading zeroes. Which one is best depends upon what other formatting you want to do.

The example from the question is quite easy since all the values have the same number of digits to begin with, so let's try a harder example of making powers of 10 width 8 too.

anim <- 25499:25504
x <- 10 ^ (0:5)

paste (and it's variant paste0) are often the first string manipulation functions that you come across. They aren't really designed for manipulating numbers, but they can be used for that. In the simple case where we always have to prepend a single zero, paste0 is the best solution.

paste0("0", anim)
## [1] "025499" "025500" "025501" "025502" "025503" "025504"

For the case where there are a variable number of digits in the numbers, you have to manually calculate how many zeroes to prepend, which is horrible enough that you should only do it out of morbid curiosity.

str_pad from stringr works similarly to paste, making it more explicit that you want to pad things.

str_pad(anim, 6, pad = "0")
## [1] "025499" "025500" "025501" "025502" "025503" "025504"

Again, it isn't really designed for use with numbers, so the harder case requires a little thinking about. We ought to just be able to say "pad with zeroes to width 8", but look at this output:

str_pad(x, 8, pad = "0")
## [1] "00000001" "00000010" "00000100" "00001000" "00010000" "0001e+05"

You need to set the scientific penalty option so that numbers are always formatted using fixed notation (rather than scientific notation).

  c(scipen = 999), 
  str_pad(x, 8, pad = "0")
## [1] "00000001" "00000010" "00000100" "00001000" "00010000" "00100000"

stri_pad in stringi works exactly like str_pad from stringr.

formatC is an interface to the C function printf. Using it requires some knowledge of the arcana of that underlying function (see link). In this case, the important points are the width argument, format being "d" for "integer", and a "0" flag for prepending zeroes.

formatC(anim, width = 6, format = "d", flag = "0")
## [1] "025499" "025500" "025501" "025502" "025503" "025504"
formatC(x, width = 8, format = "d", flag = "0")
## [1] "00000001" "00000010" "00000100" "00001000" "00010000" "00100000"

This is my favourite solution, since it is easy to tinker with changing the width, and the function is powerful enough to make other formatting changes.

sprintf is an interface to the C function of the same name; like formatC but with a different syntax.

sprintf("%06d", anim)
## [1] "025499" "025500" "025501" "025502" "025503" "025504"
sprintf("%08d", x)
## [1] "00000001" "00000010" "00000100" "00001000" "00010000" "00100000"

The main advantage of sprintf is that you can embed formatted numbers inside longer bits of text.

  "Animal ID %06d was a %s.", 
  sample(c("lion", "tiger"), length(anim), replace = TRUE)
## [1] "Animal ID 025499 was a tiger." "Animal ID 025500 was a tiger."
## [3] "Animal ID 025501 was a lion."  "Animal ID 025502 was a tiger."
## [5] "Animal ID 025503 was a tiger." "Animal ID 025504 was a lion." 

See also goodside's answer.

For completeness it is worth mentioning the other formatting functions that are occasionally useful, but have no method of prepending zeroes.

format, a generic function for formatting any kind of object, with a method for numbers. It works a little bit like formatC, but with yet another interface.

prettyNum is yet another formatting function, mostly for creating manual axis tick labels. It works particularly well for wide ranges of numbers.

The scales package has several functions such as percent, date_format and dollar for specialist format types.

How to Add Leading Zeros to Numbers or Text with Uneven Lengths , Press Control + Enter to apply the formula to all the selected cells. Here are the steps to use this technique to add leading zeroes in Excel: Select the cells in which you want to add leading zeroes. Go to Home → Number Group and click on the dialog launcher (a small tilted arrow in the bottom right). This will open the Format Cells dialog In the Format Cells

For a general solution that works regardless of how many digits are in data$anim, use the sprintf function. It works like this:

sprintf("%04d", 1)
# [1] "0001"
sprintf("%04d", 104)
# [1] "0104"
sprintf("%010d", 104)
# [1] "0000000104"

In your case, you probably want: data$anim <- sprintf("%06d", data$anim)

Leading zeros in Excel: how to add, remove and hide, Learn how to write a simple formula to add zeros before numbers or text in a column where the values are not the same length. In Excel, the Format Cells feature also can help you pad the leading zeros to cell values to make them as same length, please do as this: 1 . Select the cell values you want to pad leading zeros, and then right click, 2. In the Format Cells dialog box, under the Number tab, select Custom in the

Expanding on @goodside's repsonse:

In some cases you may want to pad a string with zeros (e.g. fips codes or other numeric-like factors). In OSX/Linux:

> sprintf("%05s", "104")
[1] "00104"

But because sprintf() calls the OS's C sprintf() command, discussed here, in Windows 7 you get a different result:

> sprintf("%05s", "104")
[1] "  104"

So on Windows machines the work around is:

> sprintf("%05d", as.numeric("104"))
[1] "00104"

How to add LEADING ZEROS in Excel, Top 5 Ways to Add Leading Zeros in Excel. Convert a Number into a Text; Use Concatenate Function; Using REPT Function; Create a Formula with TEXT  If you need to add leading zeros to text or numbers, retype the value as text or use the Text or Concatenate function. You can also format a number as text in an individual cell by adding an apostrophe before the number, such as: '000-12-345 Numbers formatted as text can still be used in calculations,

str_pad from the stringr package is an alternative.

anim = 25499:25504
str_pad(anim, width=6, pad="0")

Add Leading Zeros in Excel, Do you have a lot of numbers with an uneven number of digits in your Excel list? Do you want to make them uniform by adding leading zeros to them? Well, it's a  Add Leading Zeros in Excel Add Leading Zeros for Numeric Values. How to add leading zeros to numbers in column A? Solution #1, using the Format Cells menu. Make a range selection A1:A7. Press CTRL+1 (Format Cells shortcut). Select Custom on the Number tab. Type a 7-digit zero number in the Fill type section, then click the OK button.

Here's a generalizable base R function:

pad_left <- function(x, len = 1 + max(nchar(x)), char = '0'){

    unlist(lapply(x, function(x) {
            paste(rep(char, len - nchar(x)), collapse = ''),


I like sprintf but it comes with caveats like:

however the actual implementation will follow the C99 standard and fine details (especially the behaviour under user error) may depend on the platform

Entering leading zeroes in Excel, By default, Microsoft Excel won't display or store leading zeros. get more from Excel (PDF) (TechRepublic); How to add a drop-down list to an  How to show leading zeroes in Excel using a custom number format. Select a cell(s) where you want to show leading zeros, and press Ctrl+1 to open the Format Cells dialog. Under Category , select Custom . Type a format code in the Type box. In most cases, you will need a format code consisting of 0

Keeping leading zeros and large numbers - Excel, Keeping leading zeros and large numbers in Excel. For example, to make a phone number more readable, you can add a dash between the international  There are several functions available for formatting numbers, including adding leading zeroes. Which one is best depends upon what other formatting you want to do. The example from the question is quite easy since all the values have the same number of digits to begin with, so let's try a harder example of making powers of 10 width 8 too.

How To Add or Pad Leading Zeros To Numbers or text In Excel?, The Text function will add zeros in front of numbers, and make the new numbers with leading zeros in certain length. Enter the formula =TEXT(A4,"000000") in a  Hits: 8916. In this short article, I’ll show you how to use Power Query and Power BI to populate values with leading zeros. The original post from 3 March seems to be deleted, so I publish here again a short version.

How to: Pad a Number with Leading Zeros, You can add leading zeros to both integer and floating-point numbers by using a custom numeric format string. This article shows how to use both  Select the cell or range of cells that you want to format. Press Ctrl+1 to load the Format Cells dialog. Select the Number tab, then in the Category list, click Custom and then, in the Type box, type the number format, such as 000-00-0000 for a social

  • Suppose you want to add n zeros before animal ids you just need to do data$anim = paste(rep(0, n), data$anim, sep = "")
  • When you say you want to "add zeros", you presumably don't want to convert your integer columns to string/categorical in order to add the zero-padding inside the data itself, you want to keep them integer and only print leading zeros when rendering output.
  • thanks alot for the great help. I used formatC to add leading zeros to my anim and it worked well.
  • formatC(number or vector, width = 6, format = "d", flag = "0") worked well (R version 3.0.2 (2013-09-25)). Thanks.
  • using formatC() in the way described above didn't work for me. It added spaces instead of zeroes. Did I do something wrong? I'm using R version 3.1.1.
  • @user1816679 Sounds like you forgot flag = "0".
  • The Details section of the ?sprintf help page describes this. "m.n: Two numbers separated by a period, denoting the field width (m) and the precision (n)."
  • Note that sprintf converts numeric to string (character).
  • Thanks for the answer. I want to make a 13-digits number to 14-digits (adding leading zero). This function doesn't seems to work for this case. It gives me an arror: Error in sprintf("%020d", 4000100000104) : invalid format '%020d'; use format %f, %e, %g or %a for numeric objects. Any suggestion?
  • Try: sprintf("%014.0f", 4000100000104)
  • sprintf is not available for R 3.4.1
  • For whatever reason, this solution no longer works for me on Linux. @kdauria's str_pad is now my go to.
  • Be very careful with str_pad as it can lead to unexpected results. i.num = 600000; str_pad(i.num, width = 7, pad = "0") will give you "006e+05" and not "0600000"