Reply With QuoteAre the cells containing those numbers with leading zeroes formatted as Text or Custom Formatted with 000?Originally Posted by anilsharaf
Thanks: 0
Likes: 0
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
Re: concatenate digits begining with zero and show the result with zero.
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.
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.
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.
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.
Re: concatenate digits begining with zero and show the result with zero.
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.
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.
Re: concatenate digits begining with zero and show the result with zero.
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.
Like this thread? Share it with others