Last Friday, January 8th, would have been Elvis Presley’s 75th birthday. Sadly, he died in 1977, so he never had a chance to work with Microsoft Excel. A Twitter challenge on the weekend was to rename some of Elvis’ hit songs, using Excel references. Who says Twitter is just a waste of time?
One of my contributions was “A Little Less Concatenation,” based on Elvis’ 1968 song, “A Little Less Conversation,” that was re-released in 2002. If you’d like to hear Elvis sing the original version, and see what parties looked like in 1968, there’s a video at the end of this post.
The CONCATENATE Function
With Excel’s CONCATENATE function, you can combine text and numbers into one string. For example, this formula would combine the values in cells A2 and B2.
You can also use the ampersand operator – & – to combine text and numbers in Excel. In this example I’ve left spaces before and after the &, to make it easier to see, but you can omit them.
=A2 & B2
As you can see, it’s much easier and quicker to type & , so that’s what I use, instead of the CONCATENATE function. So, in Elvis’ honour, here are some ways that you can combine text and numbers in Excel, with a little less CONCATENATE function.
Combine First and Last Names
If you have last names in column A and first names in column B, you can combine them in column C, to create a full name. For example, to combine the last name in A3 with the first name in B3, use this formula:
=B3 & A3
In most cases, you’d want to show a space character between the first and last names. To do that, you can include a space character in quote marks in the formula:
=B3 & ” ” & A3
Combine Text and Dates
You can also combine text with numbers or dates, but the number and date formatting is lost. In this example, the dates in column B are formatted as dd-mmm-yyyy. When combined with the text in column A, the date’s serial number appears, instead of the formatted date.
To add formatting to dates and numbers, you can use the TEXT function. In the TEXT function, you refer to a cell, and specify the date or number format that you want to use. To keep the dd-mmm-yyyy format, you can use this formula:
=A3 & ” ” & TEXT(B3,”dd-mmm-yyyy”)
There are a few more formatting examples for the TEXT function on the Contextures website: Combine Data in Excel
A Little Less Conversation (1968 version)
As promised, here’s Elvis singing the original version of “A Little Less Conversation” in the 1968 movie, “Live A Little, Love A Little.” The next year there was a big festival at Woodstock, and parties eventually became a little less formal.