+ Reply to Thread
Results 1 to 3 of 3

Concatenate and keep preceeding zeros

  1. #1
    Wrkn4alivn
    Guest

    Concatenate and keep preceeding zeros

    I have tried everything to convert 5 columns into a single string. The
    problem is that these cells contain preceeding zeros, and when I try to
    convert them into a string using "&" or "concatenate" the preceeding
    zeros are deleted.

    Ex. Col. 1 Col.2 Col.3 concatenate or string
    I get
    000321 032140 001547 321321401547



    Please help.


  2. #2
    Elkar
    Guest

    RE: Concatenate and keep preceeding zeros

    The problem is that the value of your cells is 321, 32140, and 1547. The
    leading zeros are comming from cell formatting. Thus, if a funcion
    references these cells, it only picks up the stored value, not what is
    displayed.

    To get around this, you'll need to tell the formula to reference the
    displayed value, rather than the actual value. Try this:

    =TEXT(A1,"000000")&TEXT(A2,"000000")&TEXT(A3,"000000")

    The result will be a text value. If you need it to be a number, then
    enclose it in a VALUE() funcion.

    =VALUE(TEXT(A1,"000000")&TEXT(A2,"000000")&TEXT(A3,"000000"))

    HTH,
    Elkar

    "Wrkn4alivn" wrote:

    > I have tried everything to convert 5 columns into a single string. The
    > problem is that these cells contain preceeding zeros, and when I try to
    > convert them into a string using "&" or "concatenate" the preceeding
    > zeros are deleted.
    >
    > Ex. Col. 1 Col.2 Col.3 concatenate or string
    > I get
    > 000321 032140 001547 321321401547
    >
    >
    >
    > Please help.
    >
    >


  3. #3
    Bob Phillips
    Guest

    Re: Concatenate and keep preceeding zeros

    =TEXT(A1,"000000")&TEXT(B1,"000000")&TEXT(C1,"000000")

    --

    HTH

    Bob Phillips

    (remove nothere from the email address if mailing direct)

    "Wrkn4alivn" <[email protected]> wrote in message
    news:[email protected]...
    > I have tried everything to convert 5 columns into a single string. The
    > problem is that these cells contain preceeding zeros, and when I try to
    > convert them into a string using "&" or "concatenate" the preceeding
    > zeros are deleted.
    >
    > Ex. Col. 1 Col.2 Col.3 concatenate or string
    > I get
    > 000321 032140 001547 321321401547
    >
    >
    >
    > Please help.
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1