Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenate (2000)

    I have 2 columns that I want to make one. In one column I have 6 digit numbers with a leading 0 - EX: 050162. In the other column is a 2 digit number defined as a text field. To put the 0 on the 050162 number I used a custom format "0"# which put the 0 in front of the number. In a third column I have the formula =concatenate(a2,b2). It concatenates but the leading 0 drops off. I tried to use the "0"# but it doesn't work. I have tried to format the column general but nothing works. How do I put a 0 in front of this number once I concatenate? Thanks for your help.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Concatenate (2000)

    <P ID="edit" class=small>(Edited by rory on 26-Nov-02 14:44. )</P>Hi Linda,
    Just use =concatenate(a2,0,b2) or =concatenate(0,a2,b2) depending on which bit is the number you need to concatenate the 0 to.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Concatenate (2000)

    Concatenate uses the actual values NOT the displays:

    Try:
    =TEXT(A2,"000000")&TEXT(B2,"00")

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate (2000)

    Thanks - I used =concatenate(0,a2,b2) and it worked fine.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Concatenate (2000)

    Hi Linda,

    Instead of concatenate-ing the cells, use the simpler:
    =0&A2&B2
    to get the same results.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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