Results 1 to 3 of 3

Thread: keep leading zeros on concatenate problem

  1. #1

    keep leading zeros on concatenate problem

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have data with variable length and variable number of leading zeros. I'm trying any type of concatenation or formula to keep the zeros. The cells are formated as custom ########0. Values look like this:
    aaa 123456
    bbb 005005
    ccc 043434
    ddd 000505
    I want to concatenate and get exactly the values without dropping or adding zeros like 'ddd 000505' or 'aaa 123456'
    I've tried format cells to text and it drops the zeros. I've tried = "'" & A1.
    Everything I try drops the zeros or adds one zero when none are there.

    Any ideas?



  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    ARe those zeroes in the data as a text string, or is it just formatted that way?

  3. #3
    Hi Bob thanks for the reply,

    My data example is not correct. I have variable length data with leading zeros. Data can have no leading zero, 1 zero, and sometimes more that 1 zero. I want to keep all of the zeros because my DB2 database has a text field with the leading zeros. If I strip out the zeros I will not be able to find the index in DB2. All I can tell you about the spreadsheet is that the zeros are there and that the cells are formated as Custom ########0. If I change the cells to text it strips the zeros. I'm using office 2010.

    Data looks like this.

    aaa 012345
    bbb 00050556
    ccc 0012367
    ddd 052525252
    eee 529820343

    I need to concatenate the 'numeric' text field and keep the zeros. End result will eventually be '052525252' and '0012367' so it would work to put apost around the data as well but everything I try strips out the zeroes.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts