Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: concatenate digits begining with zero and show the result with zero.

  1. #1
    New Member
    Join Date
    Apr 2014
    Location
    Janjgir,Chhattisgarh,India
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default concatenate digits begining with zero and show the result with zero.

    how should I concatenate the following. Plz show me the way. Thanks in advance. there is a long list of data. This is a sample only.

    t1 t2 t3 when concatenated it shows this I want this
    A16 321043 001 A16/321043/1 A16/321043/001
    A16 321043 011 A16/321043/11 A16/321043/011
    A16 321043 123 A16/321043/123 A16/321043/123

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: concatenate digits begining with zero and show the result with zero.

    Quote Originally Posted by anilsharaf View Post
    how should I concatenate the following. Plz show me the way. Thanks in advance. there is a long list of data. This is a sample only.

    t1 t2 t3 when concatenated it shows this I want this
    A16 321043 001 A16/321043/1 A16/321043/001
    A16 321043 011 A16/321043/11 A16/321043/011
    A16 321043 123 A16/321043/123 A16/321043/123
    Are the cells containing those numbers with leading zeroes formatted as Text or Custom Formatted with 000?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    Board Regular
    Join Date
    Jun 2017
    Location
    Nth Vic, Australia
    Posts
    593
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    3 Thread(s)

    Default Re: concatenate digits begining with zero and show the result with zero.

    Try this:

    =CONCATENATE(A2,"/",B2,"/",TRIM(RIGHT(CONCATENATE("000",C2),3)))
    You may see and know but until you tell me exactly I am blind; I cannot read your mind.
    Attachments? No, but check for on site preferences.
    Cloud sharing or other off-site links? Yes, but off-site links are fragile into the future.

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: concatenate digits begining with zero and show the result with zero.

    Here is another formula you can try...

    =A1&TEXT(B1,"\/00000\/")&TEXT(C1,"000")

    The above formula assumes your numbers in Columns B could possibly begin with zero. If that is not the case, then you could use this instead...

    =A1&"/"&B1&TEXT(C1,"\/000")
    Last edited by Rick Rothstein; Jul 2nd, 2017 at 02:24 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: concatenate digits begining with zero and show the result with zero.

    1.

    =A2&"/"&B2&"/"&IF(ISNUMBER(C2),TEXT(C2,"000"),C2)

    If any cell is empty in A2:C2, we would need to adapt the formula for that.

    2.

    If you have the TEXTJOIN function on your system:

    =TEXTJOIN("/",TRUE,A2:B2)&IF(C2="","","/"&IF(ISNUMBER(C2),TEXT(C2,"000"),C2))
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: concatenate digits begining with zero and show the result with zero.

    Quote Originally Posted by Aladin Akyurek View Post
    =A2&"/"&B2&"/"&IF(ISNUMBER(C2),TEXT(C2,"000"),C2)
    Why the ISNUMBER test? It seems that the TEXT function will take a text-number and convert it to a real number in order to apply the number format to it.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    New Member
    Join Date
    Apr 2014
    Location
    Janjgir,Chhattisgarh,India
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: concatenate digits begining with zero and show the result with zero.

    Colm t1 and t2 are formated as text and colm t3 is custom formatted "000" and Ur 2nd Formula worked Fine. Many many Thanks.
    Last edited by anilsharaf; Jul 2nd, 2017 at 03:00 AM.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: concatenate digits begining with zero and show the result with zero.

    Quote Originally Posted by Rick Rothstein View Post
    Why the ISNUMBER test? It seems that the TEXT function will take a text-number and convert it to a real number in order to apply the number format to it.
    Yes, it does, so the ISNUMBER test can be dropped...

    =TEXTJOIN("/",TRUE,A2:B2)&IF(C2="","","/"&TEXT(C2,"000"))
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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