Concatenating date with a string in Excel

Question

I have two cells in Excel  one has a string and the other one has a date  in the third cell I want to put the date and the string together   For example   A1    This   A2    03 03 1982    I want A3 to be   This 03 03 1982   when I try to put this in the A3 formula    A1  amp  A2 it returns some funny numerical value for the date and does not give me the literal date

User · Answer

This is the numerical representation of the date. The thing you get when referring to dates from formulas like that.

You'll have to do:

= A1 & TEXT(A2, "mm/dd/yyyy")

The biggest problem here is that the format specifier is locale-dependent. It will not work/produce not what expected if the file is opened with a differently localized Excel.

Now, you could have a user-defined function:

public function AsDisplayed(byval c as range) as string
  AsDisplayed = c.Text
end function

and then

= A1 & AsDisplayed(A2)

But then there's a bug (feature?) in Excel because of which the .Text property is suddenly not available during certain stages of the computation cycle, and your formulas display #VALUE instead of what they should.

That is, it's bad either way.

User · Answer

You can do it this simple way       A1   Mahi   A2   NULL blank    Select A2 Right click on cell --  Format cells --  change to TEXT  Then put the date in A2  A2  31 07 1990   Then concatenate it will work  No need of any formulae       CONCATENATE A1 A2       mahi31 07 1990    This works on the empty cells ie  Before entering the DATE value to cell you need to make it as TEXT

User · Answer

Don t know if it s the best way but I d do this    A1  amp  TEXT A2  mm dd yyyy     That should format your date into your desired string   Edit  That funny number you saw is the number of days between December 31st 1899 and your date  That s how Excel stores dates

User · Answer

Another approach   CONCATENATE  Age as of    TEXT TODAY    dd-mmm-yyyy      This will return  Age as of 06-Aug-2013

User · Answer

Thanks for the solution    It works  but in a french Excel environment  you should apply something like  TEXTE F2  jj mm aaaa     to get the date preserved as it is displayed in F2 cell  after concatenation  Best Regards

[excel] Concatenating date with a string in Excel

Examples related to excel

Examples related to worksheet-function