Results 1 to 6 of 6

Thread: Concatenate

  1. #1
    Star Lounger skip's Avatar
    Join Date
    May 2002
    Location
    Connecticut, USA, Connecticut, USA
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Office 2000
    I need to add zeros to create a five digit code (ex. 000234, instead of 234). I created a column that contains 000 formatted as text. My formula is =CONCATENATE(g9,f9). When I tab out of the cell the result is =CONCATENATE(g9,f9) not 000234.
    Thanks in advance!

    Skip
    Skip Whitten, MCITP
    http://www.skipwhitten.info

  2. #2
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Check the format of your destination cell, it seems to be text, so what you type is exactly reproduced as is. Format your cell as general, it should return the right code. BTW, as simpler formula is =RIGHT("00000" & F9, 5) which can deal with codes in column F of 1 to 5 digits and return a string of 5 characters padded on the left with 0's.
    This eco-post is made of recycled electrons

  3. #3
    Star Lounger skip's Avatar
    Join Date
    May 2002
    Location
    Connecticut, USA, Connecticut, USA
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks so much. Your suggestion will save me time as well.

    Skip
    Skip Whitten, MCITP
    http://www.skipwhitten.info

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Why not just use the Text Function

    =TEXT(Cell,"Format")


    For Example if A1 contain 123 then =TEXT(A1,"00000") produces 00123

    If you want 6 digits use "000000"
    Andrew

  5. #5
    Star Lounger skip's Avatar
    Join Date
    May 2002
    Location
    Connecticut, USA, Connecticut, USA
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by AndrewKKWalker View Post
    Why not just use the Text Function

    =TEXT(Cell,"Format")


    For Example if A1 contain 123 then =TEXT(A1,"00000") produces 00123

    If you want 6 digits use "000000"
    Yes that would work as well. I still needed to add a three digit alpha code to the front as well (ex. abc00123). I might not have explained that fully.

    Thanks,

    Skip
    Skip Whitten, MCITP
    http://www.skipwhitten.info

  6. #6
    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
    ="abc"&TEXT(A1,"00000")

    Steve

Posting Permissions

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