# Elvis Sings Excel: A Little Less Concatenation

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.

**=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**

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”)**

### 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?