Concatenate

khan16

New Member
Joined
Dec 16, 2014
Messages
33
Hello,

need help concatenating/join the following to produce the example tracking # which is a combination of all three field

Client Tracking No
BATDAT
BATSEQ
SEQNBR
11151100101
11/15/11
001
001l

12/08/11
002
001l
01/03/12
003
001l
01/03/12
009
002l
01/06/12
001
001l
01/10/12
006
002l
<tbody> </tbody>

The date format is mmddy
so the tracking# is a combination of the date and the BATSEQ BATDAT & SEQNBR

Thanks...
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Examples are easier to follow when values are not repeated. What would the value in cell A1 be if these were your values...

Client Tracking NoBATDATBATSEQSEQNBR
???????????
01/23/45
678
901l




<tbody>
</tbody>

Note: I am unclear as to why the SEQNBR value apparently dropped the first zero (you put 01 in the answer rather than 001 which is what I would have expected.
 
Upvote 0
Hi All,

Thanks for the formula yesterday RonB but they have added another requirement.

This formula you provided worked : =TEXT(BD2,"mmddyy")&BE2&MID(BF2,2,2) produces this result 1115111

what if I need it to produce 111511001001


pulling from this table for instance:

BATDAT
BATSEQ
SEQNBR
11/15/11
001
001l
<tbody> </tbody>
Thanks again










<tbody> </tbody>


 
Upvote 0
Hi All,

Thanks for the formula yesterday RonB but they have added another requirement.

This formula you provided worked : =TEXT(BD2,"mmddyy")&BE2&MID(BF2,2,2) produces this result 1115111

what if I need it to produce 111511001001


pulling from this table for instance:

BATDATBATSEQSEQNBR
11/15/11
001
001l

<tbody>
</tbody>

If BATSEQ is always a three-digit number, and if the number in front of the | symbol is always three digits long, then try this...

=TEXT(BD2,"mmddyy")&BE2&LEFT(BF2,3)
 
Upvote 0
Thanks for the quick response Rick. It worked.
Is it possible to show or pad with those zeros so it will show as 111511001001

The zeroes are not actually recorded in the cell. Any thoughts...

Thanks
 
Upvote 0
Thanks for the quick response Rick. It worked.
Is it possible to show or pad with those zeros so it will show as 111511001001

The zeroes are not actually recorded in the cell. Any thoughts...

Try this....

=TEXT(BD2,"mmddyy")&TEXT(BE2,"000")&TEXT(LEFT(BF2,3),"000")
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top