Excel - concatenate a range

[Killer42]

Is there a simple way in Excel 2003 to concatenate a range of cells, rather than individual cells?

In other words, I want to enter =CONCATENATE(D2:AN2) in a cell, but this produces an error. How can I work around it?

[Stang02GT]

Hey Killer,

I did a quick google search on what you were looking for and found some good articles that explain how to do it... here they are..


Excel Tips

MS Excel

Hope that helps you !

[Killer42]

Thanks for that. Think I'll go with the user-defined CONCAT function.

[Stang02GT]

Sure thing. I don't know if those links helped you?! But I figured I would at least try and find something to help ya out.

[Killer42]

Hahahaha... it's got a bug in it. :D

The user-defined function in that "Excel Tips" returns #VALUE! if you include the optional delimiter, and the cells in the passed range are empty. This is because when doing the concatentation it adds an extra delimiter on the end. There's an extra piece of code to then remove it. But if nothing was concatenated, it tries to remove the last 'n' characters of an empty string, and doesn't work.

Here's a corrected version...

Function Concat(myRange As Range, Optional myDelimiter As String) As String
  Dim r As Range
  Application.Volatile
  For Each r In myRange
    If Len(r.Text) Then
      Concat = Concat & IIf(Concat <> "", myDelimiter, "") & r.Text
    End If
  Next
End Function
 

By the way, is this like Word where I can move macros to the Normal template so they're always available? Couldn't work it out from the doco.