CONCATENATE Function

[bwils39]

I am having trouble using the CONCATENATE FUNCTION with a particular date format. The cell in question is A2, which is a date in the format YYMMDD. Here are the contents of the worksheet:

A1: AAPL
A2: 170329
A3: C
A4: 00120
A5: 000

I am attempting to merge the contents of A1:A5 in cell A6 so that it reads "AAPL170329C00120000". When I do this using the CONCATENATE function the date in A2 reads as some random number. I've tried messing around with the TEXT function, but I cannot get it to read as I'd like. Any thoughts?

[FlameRetired]

Try this

Please Login or Register  to view this content.

[MrShorty]

It might be helpful to see what you have tried that has failed, because it seems to me that the right combination of CONCATENATE() and TEXT() should do this.

Assuming that A2 is a date serial number (the underlying value is 42823), then something like =TEXT(A2,"yymmdd") should return "170329". What did you try, and what did it return?

[FDibbins]

Hi, welcome to the forum

When I do this using the CONCATENATE function the date in A2 reads as some random number.

Actually that really IS the date - as excel sees it.
a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Wed Mar 2017) is actually 42823

However, when you use concat (or left, right, mid) function, it converts the answer to text, no matter what it contains, so it is converting the date to what excel actually sees as the date.

My personal preference is to use & instead of CONCATENATE, it is less typing, for 1 thing.

=A1&TEXT(A2,"yymmdd")&A3&A4&A5