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

[anilsharaf]

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

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

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

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

Are the cells containing those numbers with leading zeroes formatted as Text or Custom Formatted with 000?

 

[BrianJN1]

Try this:

=CONCATENATE(A2,"/",B2,"/",TRIM(RIGHT(CONCATENATE("000",C2),3)))

 

[Rick Rothstein]

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")

 

[Aladin Akyurek]

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))

 

[Rick Rothstein]

=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.

 

[anilsharaf]

Colm t1 and t2 are formated as text and colm t3 is custom formatted "000" and Ur 2nd Formula worked Fine. Many many Thanks.

 

[Aladin Akyurek]

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"))