Excel VBA Concatenate Function - The Reverse of Text to Columns - Code and File Included
**Get the Excel file here
https://chrisjterrell.com/blog/215568...
**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-v...
Excel VBA Concatenate Function - The Reverse of Text to Columns
Concatenation is joining two strings together into a single string. A simple formula in a cell can do this, and it would look like this.
Similarly, you can concatenate strings in VBA. The example below makes s equal to "This is Text " and adds whatever is in Cell(1,1) or A1 on the activesheet
s = "This is Text " & Cell(1,1)
While this is all well and good but we can do better. Excel has a feature called "Text to Columns," but it doesn't have a function that does the opposite.
This code below allows you to reverse the "Text to Columns" in a function.
NOTE: A VBA function works just like other Excel Functions but with a couple of important caveats. The first caveat is that the function has to in the workbook. In other words, if you have a function in Book1, it will not work in Book2 unless the Function Code is copied to Book2. The second caveat is that Macros will need to be enabled for the function to work. All functions are static unless changed so that it might display the correct info, but if macros are disabled, it will not recalculate.
The Code
===========================
Function concatRange(rng As Range, delimit As String)
c = ""
For Each cl In rng
c = c & cl & delimit
Next
concatRange = Trim(c)
End Function
