Excel VBA Concatenate Function - The Reverse of Text to Columns - Code and File Included
**Get the Excel file here
https://chrisjterrell.com/blog/215568/vba-concatenate
**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
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
Видео Excel VBA Concatenate Function - The Reverse of Text to Columns - Code and File Included канала EverydayVBA
https://chrisjterrell.com/blog/215568/vba-concatenate
**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
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
Видео Excel VBA Concatenate Function - The Reverse of Text to Columns - Code and File Included канала EverydayVBA
vba concatenate loop vba concatenate cells loop vba concatenate function excel vba concatenate cells with delimiter excel vba concatenate range with separator vba concatenate function example excel vba concatenate range function Concatenate function in vba macro how to concatenate in excel vba vba concatenate range vba concatenate range of cells vba concatenate string function vba concatenate cells excel vba concatenate cell values excel vba everydayvba
Комментарии отсутствуют
Информация о видео
11 августа 2020 г. 16:00:06
00:03:44
Другие видео канала