Appending or prepending something is called concatenating in Excel. Concatenating is a big word for combining things in one cell. You could be trying to combine text and data, or two types of data, or two cells of text\data together into one cell. We’ll be discussing how to concatenate text with date data.
How to add text before or after a function in the same cell:
Let’s produce today’s date in an cell. For that, we’ll use the following function:
Alone, this will produce today’s date in the “mm/dd/yyyy” format. But what if we wanted to add words before or after it. Let’s try to add the words “Lasted Updated: ” before the function. You can add any text you’d like before or after any function as long as you wrap it in quotes and join them with the “&” symbol. Like so:
="Last Updated: "&TODAY()
This will get you close, but unless you’ve seen the way excel formats dates before you’ll be scratching your head when you see the output “Last Updated: 41261” or whatever the serial assigned to the date is.
Excel stores dates and times based on a number that can be calculated. Excel calls the numbered assigned to the date a “serial date.” This may help when doing calculations which depend on the date, but this tutorial’s goal is to simply add text before or after a friendly display of the date. We definitely want our date display dynamically, but we’ll need to convert the data to text using the TEXT() function in order to display it properly. The text function needs to first know what you’re going to be converting to text, then it will need to know how to format the text. So let’s tell excel to show today’s date in mm/dd/yyyy format, and prepend “Last Updated: ” before it. The field will look like this:
="Last Updated: "&TEXT(TODAY(),"mm/dd/yyyy")
This will output exactly what you are looking for. From the inside out: We took today’s serial date, converted it to text and then formatted that text as mm/dd/yyyy and then prepended “Last Updated: ” to it.
- Text Function – http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx
- Today Function – http://office.microsoft.com/en-us/excel-help/today-function-HP010062297.aspx
Let me know your experiences with this.