Home / User Contributed Macros / Split Function

Split Function

The following macro splits the strings by comma. It can be used to split strings based on other values(space, quote, etc.) too by modifying line no 18.

Strings with comma are present in Sheet1.A1:A3 and separated strings are placed in Sheet2.

  1. Public Sub split_function()

  3.     'Declarations
  4.     Dim splitArr() As String
  5.     Dim sourceRng, destRng As Range
  6.     Dim destRow, destStartCol, destEndCol As Integer
  7.     Dim destSheet As String
  8.  
  9.     'Assigning Initial values
  10.     Set sourceRng = Sheet1.Range("A1:A3")     '  CommaSeperatedValues' Range 
  11.     destRow = 1
  12.     destStartCol = 1
  13.     destSheet = "Sheet2"
  14.  
  15.     For each cell in sourceRng                 ' Looping through all the cells in the source range
  16.  
  17.         If Not(IsEmpty(cell))    Then
  18.             splitArr = split(cell, ",")            '  Splitting the values
  19.  
  20.             destEndCol = UBound(splitArr) + 1
  21.  
  22.             'split values are pasted in destination range
  23.             Sheets(destSheet).Select                    
  24.             Set destRng = Sheets(destSheet).Range(Cells(destRow, destStartCol), Cells(destRow, destEndCol))
  25.             destRng.value = splitArr
  26.         End If
  27.  
  28.         destRow = destRow +1
  29.  
  30.     Next cell
  31.  
  32. End Sub




e.g


After splitting


    		 RSS of this page