Concatenate with leading zeros

  • I need to format a single column of numbers to always have 4 digits. If the cell contents is less than 4 digits, then I need it to concatenate with preceding zeros to bring the total number of digits always to 4.

    so...

    3205 is ok
    905 is not ok....need it to be 0905.

    I need to concatentate vs converting to text so the value includes the zeros. These numbers are constantly being input via web query, so a formula in the cell will get wiped out. Possibly need a vba macro to run after the query has finished.

    Thanks for any help.

  • Re: Concatenate with leading zeros


    yes, that is a good way to have the values APPEAR with the correct number of digits, however if you are manipulating the numbers they will only have what they were input with. I am using a LEFT function down the road with these numbers...it needs to have 4 digits..always....not just appear to have 4 digits.

  • Re: Concatenate with leading zeros


    The result will be text, not a number, but the formula =TEXT(A1,"0000") will do what you want.


    Note, that the number 0123 is exactly the same as the number 123 so no number has "leading zeros", only numerals (text strings that represent numbers) have leading 0's.

  • Re: Concatenate with leading zeros


    As mikerickson has said, you will need to convert these numbers to Text values. If you want to do this via a macro rather than messing around with inserting columns in your sheet, highlight the column (or part of the column) you want to modify and run the SelectionPadLeft macro below:

    This code will only modify cell values that are numeric to begin with.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!