Concatenating date with a string in Excel

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 & A2 it returns some funny numerical value 开发者_开发技巧for the date and does not give me the literal date.

---

Don't know if it's the best way but I'd do this:

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

---

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.

---

Another approach

=CONCATENATE("Age as of ", TEXT(TODAY(),"dd-mmm-yyyy"))

This will return Age as of 06-Aug-2013

---

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

---

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

---

I found that for this situation, the simplest solution is to define a Custom number format for the cell containing the date. The format in this case should be:

"This:" mm/dd/yyyy

To set this format:

1. Right click on the cell
2. Select Format Cell
3. Select Number tab (should be displayed by default)
4. Pick Custom from the Category list
5. Specify the format in the "Type" field
6. Press OK

Note: If you really want the preceding text to be picked from a cell, this solution will not work as described.