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. Otherwise, he might have sung “A Little Less Concatenation”, instead of “A Little Less Conversation.”
Elvis’ 1968 song, “A Little Less Conversation,” 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.
=CONCATENATE(A2,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
Add Space Character
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.
Use TEXT Function
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”)
Formatting Examples
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.
_______
Unfortunately Excel has always lacked a CONCATENATE function that would merge values from a range of cells simply by selecting the range (maybe they have this in XL2007?). Several people have written UDFs to achieve this. Here’s a robust one from MVP Chip Pearson that has lots of error trapping:
http://www.cpearson.com/Excel/stringconcatenation.aspx
Jason, thanks for the link to Chip’s UDF. The Concatenate function hasn’t changed in Excel 2007.
Here is a UDF concatenation function that I have posted to the Excel newsgroups in the past. It takes a delimiter argument (which can be any length) followed by a variable number of ranges and/or text strings in any combination. I took a quick look at Chip’s function and note that it looks more complete than mine; but, for simple range/text concatenations, it should be more than sufficient. The code approach I use is completely different from the one Chip uses and it does not provide for this UDF to be usable in an array formula like his code does (although, quite frankly, I am having trouble imagining a scenario where it could be used with an array… how do you pass an array into the function and distinguish that it is not a simple range?). Anyway, for those who are interested in it, here is my UDF…
Function BigConcat(Delimiter As String, ParamArray Data()) As String
Dim X As Long, Z As Long, IR As Range
For Z = LBound(Data) To UBound(Data)
If TypeName(Data(Z)) = “Range” Then
For X = Data(Z)(1).Row To Data(Z)(1).Row + Data(Z).Rows.Count – 1
Set IR = Intersect(Data(Z), Rows(X))
If IR.Count = 1 Then
BigConcat = BigConcat & IR.Value & Delimiter
Else
BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Intersect( _
Data(Z), Rows(X)))), Delimiter) & Delimiter
End If
Next
Else
BigConcat = BigConcat & Data(Z) & Delimiter
End If
Next
Do While InStr(BigConcat, Delimiter & Delimiter)
BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter)
Loop
BigConcat = Left(BigConcat, Len(BigConcat) – Len(Delimiter))
If InStr(BigConcat, Delimiter) = 1 Then
BigConcat = Mid(BigConcat, Len(Delimiter) + 1)
End If
End Function
Thanks Rick, it’s interesting to see different approaches to a challenge.
Hi,
I have gone through the “Userform creation in Excel” session.
But I couldn’t understand how you did the coding of the form.
How to write those instructions?