Thanks:  0
Likes:  0

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

1. ## 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. ## Re: concatenate digits begining with zero and show the result with zero.

Originally Posted by 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
Are the cells containing those numbers with leading zeroes formatted as Text or Custom Formatted with 000?

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

Try this:

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

4. ## 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")

5. ## 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))

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

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

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

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

Originally Posted by Rick Rothstein
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"))

## User Tag List

#### Posting Permissions

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