Elvis Sings Excel: A Little Less Concatenation

Concatenation in Excel

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

Combine First and Last Names
Combine First and Last Names

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

CombineNames02

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.

CombineDates01

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

CombineDates02

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.

_______

0 thoughts on “Elvis Sings Excel: A Little Less Concatenation”

  1. 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

  2. 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?

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.