Leading Zeros in a Cell

    Is there a way to format cells to except leading zeros?


    I'm using the "& A1 &" formula to help created a specific file format. The file format requires that some values require a certain length and if the value does not meet this length then the value should be proceeded by Zero.
    Example: Require characters is 10, but the values is 7054. 7054 would be enter into the file format as 0000007054.


    How can or can I format excel utilize leading zeros, other than making it a text cell because the value must be calculated?

    Re: Leading Zeros in a Cell


    That worked for the individual cell but in the cell I use the "& A1 &" it reverts back to 7054 instead of 0000007054.

    Re: Leading Zeros in a Cell


    Hi Slick,


    This looks suspiciously like an SAP customer or vendor number..... :)


    This is a common problem when downloading or uploading data from systems such as the above where numbers like this are normalised to a standard length by padding them out with zeroes.


    What I do in such cases (if this is indeed the case), is prepend the string with an apostrophe (')


    I make use of the VAL command, the STR and the RIGHT string processing commands to then format the string.


    So, to get the numeric value you would use something like:

    Code
    Val(Mid(Range("A1").value,2))


    To format it you would use:


    Code
    Range("A1").value = "'" & Right("0000000000", & Str(my_number),10)


    Regards


    Rich

    Regards


    Rich

    Re: Leading Zeros in a Cell


    Will, sorry about that last message.
    I'm trying to create a File formatter using excel. I'm using the formula "& <Cell> &" to allow the users to entered the needed parameters. But some of the paramers are totals and must be 10 digits in length (leading zeros are used if needed, example: 0000007054 instead of 7054). The custom format works great in the cell that I'm doing the actual totals but in the cell that I reference that total using the "& <Cell> &" it reverts back to 7054 instead of 0000007054. I'm trying some of the suggestion that were posted after I signed off yesterday. Thanks Will in advance for any advice and my apologies if I wasn't very clear.

    Re: Leading Zeros in a Cell


    Hi,


    I am not able to print data with leading zero in desired column. Pls see the VBA code below -


    srecord = Mid(textline, 25, 4) ===> Here srecord got the value 0431 from textline input and I need to print the 0431 in certain column. I am trying below code -
    Range("A" & i).Offset(1, 1) = Mid(textline, 25, 4) OR
    Range("A19").Value = Srecord OR
    Range("A19").Value = CStr(srecord)


    But nothing is showing value as "0431" instead showing 431. I need value 0431. I can get the same value if I change the format of said column by right click but I don't want to do so as my column A19 will not be fix.


    Please help.


    Regards,
    Manoj Kaushik

Participate now!

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

Register Yourself Login