MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

How to LPAD in Excel

with 21 comments

Somebody tweeted tonight and asked how to left pad a string in Microsoft Excel 2007. I thought it was an interesting question, and replied. Then, I thought oops … it should be on the blog.

You can use a combination of the REPLACE and REPT (believe it or not that’s the repeat function). The first step to left pad requires you to master the REPLACE function. It takes four actual parameters. The first is the old or base string, which is typically a cell reference. The second is the starting position, the third is the length of the base string to replace, and fourth the new string. The following example assumes you want to replace the whole string with a white space before the original string.

=REPLACE(A1,1,LEN(A1)," "&A1)

If you don’t like using the ampersand to concatenate, you can replace it with the CONCATENATE function, like this:

=REPLACE(A1,1,LEN(A1),CONCATENATE(" ",A1))

While more compact, the ampersand does the trick and ultimately saves space for complex mega formulas. The next step requires you learn how to use the REPT function.

The REPT function takes two parameters, which are the string you want to repeat and the number of times you want to repeat it. Assuming that you expect all strings to be smaller than twenty, you can use the REPT function this way to create a dynamic padding of physical white space.

=REPT(" ",20-LEN(A1))

When you put the concepts together, you’d get the following command to dynamically replace a string in cell A1 with a 20 character cell that is right aligned. The new value would be in the cell where you put this formula.

=REPLACE(A1,1,LEN(A1),REPT(" ",20-LEN(A1))&A1)

This lets you left pad a string, which I suppose is great when you want to output a flat position specific file. I’d prefer a CSV and a more robust programming environment but I hope it helps those who want to do it.

As Andy pointed out, you can simplify it with this:

=REPT(" ",20-LEN(A1))&A1

As an update to the original blog post, here’s a Visual Basic for Applications (VBA) User Defined Function (UDF) that would provide you with an add-in function to perform this task. It’s designed to LPAD numbers or text, and always return text. You can find how to develop and deploy Excel VBA UDFs in this more recent blog post of mine.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
' The function takes any input as a cell and manages numbers different than strings.
Function LPAD(cell As Variant _
             , Optional offset As Variant = 0 _
             , Optional padWith As String = "0")
 
  ' Return variable.
  Dim returnString As String
  Dim whiteSpace As String * 1
 
  ' Reports a meaningful error when a non-numeric offset parameter is provided as the offset call parameter.
  If Not IsNumeric(offset) Then
 
    ' This means the function is called incorrectly but suppresses a #VALUE! error.
    MsgBox ("Offset should not be a non-numeric value of [" + offset + "]!")
 
  ' Dynamically assign the offset for the optional offset variable.
  ElseIf offset = 0 Then
    offset = Len(cell)
  Else
    ' This allows you to see the offset value when it is provided as a number.
    ' MsgBox ("offset is [" + CStr(offset) + "]")
  End If
 
  ' Assign default value.
  whiteSpace = " "
 
  ' Ensure size is at least the length of the cell and padding value.
   If IsNumeric(offset) And Len(cell) > offset Then
    offset = Len(cell) + Len(padWith)
   End If
 
  ' Assign default padding value when cell is a number.
  If IsNumeric(cell) And IsNumeric(padWith) Then
    padNumberWith = CInt(padWith)
  Else
    padNumberWith = 0
  End If
 
  ' Convert to string when numeric, use padWith text value when not null and whitespace if null.
  If IsNumeric(cell) Then
    returnString = Application.Rept("0", offset - Len(Application.Text(cell, padNumberWith))) + CStr(cell)
  ElseIf padWith <> "0" Then
    returnString = Application.Rept(padWith, offset - Len(cell)) + cell
  ElseIf padWith = "0" Then
    returnString = Application.Rept(whiteSpace, offset - Len(cell)) + cell
  Else
    returnString = Application.Rept(" ", offset - Len(cell)) + cell
  End If
 
  ' Return formatted string.
  LPAD = returnString
 
End Function

The formula for a number is different than it would be natively in Excel. That’s because some behaviors are implicitly provided. The equivalent function in Excel is:

=REPT("0",20-LEN(TEXT(A6,"0")))&A6

Microsoft Excel performs the LEN() function implicitly if you leave it out. For example, this works the same as the more complete formula above.

=REPT("0",20-(TEXT(A6,"0"))&A6

If you forget the LEN() function call in the VBA module, it raises an Excel Error 20150. This error is suppressed by a #VALUE! error. That error is returned because an error message can’t be concatenated with a string. Once you identify where the error occurs you can enclose it in a CStr() function call. The CStr() function explicitly casts the Err.number value to a string, which is then returned to the worksheet in the cell where you’ve called the function.

All that being said, it’s not nearly that complicated. You can provide the LPAD UDF in a simpler function that doesn’t use the built-in workbook functions. All you need to know is how to us the String() function, like this:

Function LPAD(padding AS Variant, character AS String, cell As String)
 
  ' Define and assign a default value, which must be a string data type.
  Dim character_default As String * 1
  character_default = " " 
  Dim number_default As String * 1
  number_default = "0" 
 
  ' Check if you've got adequate values to proceed.
  If IsNumeric(padding) And IsNumeric(cell) Then
    If IsNumeric(character) Then
      LPAD = String(padding, character) + cell
    Else
      LPAD = String(padding, number_default) + cell
    End If
  Else
    If IsNumeric(character) Then
      LPAD = String(padding, character) + cell
    Else
      LPAD = String(padding, character_default) + cell
    End If
  End If
 
End Function

You would call this UDF with any of the following function calls. You skip the last parameter because its an optional parameter.

User Defined Function Calls
Cell Formula
A2 =LPAD(A1,10)
A3 =LPAD(A1,30,"-")
A4 =LPAD(A1,20,0)

Written by maclochlainn

March 4th, 2009 at 12:17 am

21 Responses to 'How to LPAD in Excel'

Subscribe to comments with RSS or TrackBack to 'How to LPAD in Excel'.

  1. Nice trick dude. Helped at the right time, in fact saved me a lot of time.

    Thanks a Lot

    Suraj

    16 Apr 09 at 11:52 am

  2. Thanks man, you helped me a lot.

    Salustiano

    27 May 09 at 2:07 pm

  3. Thanks for this HowTo -> Great explanation! You saved me a lot of time btw ;-)

    navien

    2 Jul 09 at 10:18 am

  4. The REPLACE used in all of the formulas is redundant. It takes one string as input and replaces the entire thing with a second string, just use the second string. The following is sufficient for the final formula:

    =REPT(" ",20-LEN(A1))&A1

    Andy

    10 Jul 09 at 9:23 am

  5. Absolutely, thanks! I’ve updated the blog page. :-)

    maclochlainn

    10 Jul 09 at 9:39 am

  6. I guess to do a RPAD is to simply reverse it:

    =A1 & REPT(” “,20-LEN(A1))

    rgatech

    1 Oct 09 at 4:16 pm

  7. Thank you for your tip.
    I have a question.
    If i have a cell containing the following string 005010134 stored as text.
    I need to add a “dot” after the first 3 characters and after the next 2 and after the next 2 so the result will be 00501.01.34
    Is this possible?

    Thanks

    Wael Al Maghraby

    3 Jan 10 at 3:56 am

  8. Yes, you can do it like this, assuming the string is in cell A1:

    =CONCATENATE(LEFT(A1,5),".",MID(A1,5,2),".",MID(A1,8,2))

    You might find this early post on Excel string parsing or this one on how to use the CONCATENATION function useful.

    Cheers, Michael

    maclochlainn

    3 Jan 10 at 9:48 am

  9. Dear maclochlainn,
    Thank You very much, works perfectly.

    Wael

    Wael Al Maghraby

    3 Jan 10 at 1:12 pm

  10. Working with numbers and not strings? Try Log10 instead of Len.
    =REPT(“0″,0-log10(A1)) &A1

    As an example, you could display a number of minutes from cell A1 as hh:mm with
    =floor(A1/60,1) &”:” &REPT(“0″,2-log10(mod(a1,60))) &mod(a1,60)

    So, if A1 contained the value 67, this would output “1:07″

    Dustin

    22 Apr 10 at 3:14 am

  11. This also works (apologies if someone else has posted this already):

    If
    A1 = “123″
    B1 = “1″
    C1 = “123456″

    RIGHT(“000000″&A1,6) = “000123″
    RIGHT(“000000″&B1,6) = “000001″
    RIGHT(“000000″&C1,6) = “123456″

    Jamie

    27 Apr 10 at 4:39 pm

  12. Dustin,

    I hadn’t checked these, still haven’t on the bottom one, but the top one doesn’t appear to LPAD unless I’m missing something. This would be the equivalent with a number:

    =REPT("0",20-TEXT(A1,"0"))&A1

    I’ve also updated this with a User Defined Function that does both.

    maclochlainn

    31 May 10 at 11:29 am

  13. Great, and so easy!

    -Abhi

    Abhishek

    8 Dec 10 at 9:57 am

  14. If you’re left-padding a field and don’t want blanks or non-numbers to be padded with zeroes,
    try this, which will leave them as “”:

    =IF(ISNUMBER(A1),REPT(0,20-LEN(A1))&A1,"")

    Kathy

    12 Aug 11 at 9:48 am

  15. Hey, thanks for sharing. Helped a lot.

    TK

    24 Aug 11 at 2:34 pm

  16. Try

    =TEXT(A1,”000000″)

    that will pad A1 with a max of six 0′s.

    i.e 088888, 000088, etc.

    Much easier.

    ac

    12 Jan 12 at 4:16 pm

  17. Jamie
    Thank you so much … yours is the simplest and easiest way of doing this.
    Thanks
    A

    Anurag

    14 Feb 12 at 4:36 am

  18. just format a cell; use custom formatting, say you want to lpad a string with ’0′s. Here is an example:
    String is ’123′, what you want is ’0000123′, then the custom formatting for that cell would be 0000000, that’s it, apply that to all the cells you need and that’s it:

    1234 will become 0001234
    12345 will become 0012345
    1 will become 0000001

    Raj

    24 Feb 12 at 8:12 pm

  19. If you want that number 56 is in “000056″ just assign that like this:

    number = 56
    formatted_number = format(number, “000000″) ‘ it is string off course

    Tom

    20 Nov 12 at 7:10 am

  20. Sure, but the idea of the function was to return a string not a number. More or less you’re advocating:

    Function check()
      Number = 56
      check = Format(Number, "000000")
    End Function

    maclochlainn

    20 Nov 12 at 1:56 pm

  21. REPT may come from REPeat Text, not from REPeaT..

    Stefano Gatto

    22 Nov 13 at 7:30 am

Leave a Reply