Excel Join / Split Function

The Excel Join and Split Excel functions are very useful function. The split function is used to take a delimited string and parse the values into an array. The join function is used to extract the values out of the array and concatenate them back into a delimited string.

The Basics:

Join(Array, Delimiter)

Array = The array that contains the values you want to join

Delimiter = The delimiter we want to use

Split(StringToProcess, Delimiter, The Numer of Strings to Process, Compare Option)

StringToProcess = The string that will be proccessed

Delimiter = The delimiter that you want to use

The Numer of Strings to Process = How many strings do you want to process

Compare Option = Do you want the delimiter to be case sensitive or not

Now let’s assume I want to take the string value in cell A2, process it into an array, write out the array values in column B and then process the array values back into a delimited string specifying our own delimiter value to use.

Code

Dim A As Long    'The counter we will be using the process our records

Dim ArrayValues() As String     'Declare an array variable

Dim StringToProcess As String    'This is the string that will be processed

Dim Counter As Long   'This variable will hold the row variable to control the output where we want to write the output to

StringToProcess = ActiveSheet.Cells(2, 1).Value  'Assign the value in cell A2 to our string variable

ArrayValues() = Split(StringToProcess, ",")      'The split function parsed the comma delimited values

'into the array starting at position 0. The array values

'are dynamically populated depending on how many values

'there are

Counter = 2     'We set the counter at 2 as we want to start writing the output from row 2

'Now let's take the array values and write the array values out to cells B2 through B8.

For A = LBound(ArrayValues) To UBound(ArrayValues) 'This will allow us to step through the array values using the for next function

ActiveSheet.Cells(Counter, 2).Value = ArrayValues(A)

Counter = Counter + 1   'Increment the counter variable with 1

Next

'Now let's create a new delimited string and write the output to cell C2

ActiveSheet.Cells(2, 3).Value = Join(ArrayValues, "#")

Output