Oh Good, Another Excel Query

jghal

In a cell in excel can I have text AND a reference to another cell, ie

If the contents of cell A1 were 15, then in cell d5 i might have

"The contents of cell A1 are =(a1)"

which would show up in the cell d5 as

The contents of cell A1 are 15



Thanks in advance

[Deleted User]

Try something like this:

=CONCATENATE("The contents of cell ", CELL("address",A1), " are = ", CELL("content",A1))

Look up help on the CELL command, it's got a few different uses

jghal

Thanks Ringo_24601

Looked up the CONCATENATE function and have got it to do what I want.

A slight adjustment required though as one of the cell contents is a date formatted as dd/mm/yyyy, but CONCATENATEing (is that a word ) removes the formatting and the date is entered in the new cell as a general number

Any ideas to get the date in the correct format

Chippy_Minton

Use the TEXT function to format the date:

=CONCATENATE("Contents of A1 = ",TEXT(A1,"dd/mm/yyyy"))

[Deleted User]

Sure... try this:

=CONCATENATE("The contents of cell ", CELL("address",A1), " are = ", TEXT(A1,"dd/mm/yyyy") )

The word is concatenating

jghal

Absolutely superb

Many thanks guys

:beer:

Fairwinds

You can also shorten it to

="The contents of cell A1 are " & TEXT(A40,"dd/mm/yy")

as an & replaces the need to know how to spell concatenate