I am getting data from a reporting tool. The report contains Date
and User
columns (in that order). The tool returns dates in mm/dd/yyyy format.
Excel does not convert the imported data to a numerical date; it is stored as a text value (the raw data from the reporting tool is aligned to the left instead of to the right).
I need to concatenate User
and the numeric date value (in that order). Using the raw imported data produces a result in the form userAmm/dd/yyyy
instead of the needed format like userA42379
. The concatenation of date as an unformatted numeric value only happens correctly if the raw data is in dd/mm/yyyy format (otherwise the input data is not interpreted as a date).
I tried to convert the dates from mm/dd/yyyy format to dd/mm/yyyy format using MID
/LEFT
/RIGHT
and the number formatting in Excel but that didn't work. The date still appears in mm/dd/yyyy format when concatenated with another cell with text in it.
Any ideas?
Best Answer
You said the report contains
Date
andUser
columns (in that order).You also said the tool returns dates in text format in the form of
mm/dd/yyyy
. Let's say in that case you have the folowing:You need to concatenate
User
andDate
value (in that order). This is what we are going to do in cellC2
Now I am confused as to which way you wish the concatenated date format to be. Do you want it in
mm/dd/yyyy
ordd/mm/yyyy
do you want the date code to give an output such asuserA 42379
?I will cover all three.
For concatenating whilst maintaining the date format (
mm/dd/yyyy
), use the following formulaThe
&" "&
in the middle adds the space in between.For concatenating the date in the format (
dd/mm/yyyy
), use the following formulaFor concatenating the date code such as
42379
, use the following formula