Meraz Al Nahian
The article will provide you with some useful applications to split string(s) into cells by Excel VBA. We do this often to see necessary data separately. In this article, we are going to use a dataset where we have short information on some great footballers provided with their names and some of the clubs they played in. Also, we have their total matches and goals stats in the dataset.
Download Practice Workbook
4 Useful Applications to Split String into Cells by Using Excel VBA
1. Excel VBA to Split Comma Separated String into CellsÂ
In the dataset, we see that the club names are separated by commas. If we want to see the name of the clubs in different cells, we can split those strings using the commas between them by VBA. Let’s go through the process below. (We will omit the matches and goals stat column in this section)
- First, open Visual Basic from the Developer Tab.
- You will see the VBA window appear. Now, go to Insert >> Module.
- The VBA Module will open. Type the following code in the Module.
Sub SplitByCommas()
    Dim rng As Range
    Set rng = Selection
    rng.TextToColumns Destination:=rng(1, 1).Offset(, 1), _
    DataType:=xlDelimited, Other:=True, OtherChar:=","
End Sub
Here, we are calling our Sub Procedure as SplitByCommas and declaring rng as Range. We will split our strings via selection so we set rng to Selection. Then we set the destination to split strings by using the VBA TextToColumn method. As we want to split based on commas, we used Comma(,) as Delimiter for it.
- Now go back to your sheet, select the range C4:C9 and run the Macro named SplitByCommas as it is your current Macro.
- After that, it will split the name of the clubs into cells beside column C.
Thus you can easily split strings into cells by using the VBA TextToColumn method. You can format those cells according to your privileges.
Read More: Excel VBA: Split String into Rows (6 Ideal Examples)
2. Split Range of Strings into Cells Using Excel VBAÂ
Suppose, we want to see the Matches/Goals stat in different columns. We need to split the strings of that column into different cells to show the stats separately. Let’s see how we can do this. (We will omit the Club column for our convenience)
Steps:
- Follow Section 1 to open the VBA Module.
- Type the following code in the VBA Module.
Sub SplitMatchAndGoals()
Dim Str_Array() As String, Match_Goal As Range, i As Integer
For Each Match_Goal In Selection
Str_Array = Split(Match_Goal, "/")
For i = 0 To UBound(Str_Array)
Match_Goal.Offset(, i + 1) = Str_Array(i)
Match_Goal.Offset(, i + 1).EntireColumn.AutoFit
Next i
Next
End Sub
Code Explanation
- Here, we are calling our Sub Procedure as SplitMatchAndGoals.
- Then we declare Str_Array as String, Match_Goal as Range and i as an integer.
- We will select the strings via selection. So we set Match_Goal to Selection in the For Loop. The For Loop will go through the selected range.
- The strings will be splitted based on the Slash (/) We kept the Splitted string in Str_Array.
- Next, by using VBA offset, we placed the Splitted Strings into Cells of adjacent columns.
- Now go back to your sheet, select the range C4:C9 and run the Macro named SplitMatchAndGoals as it is your current Macro.
- After that, it will split the match and goal stats into cells beside column C.
Thus you can easily split strings into cells by using the VBA TextToColumn method. You can format those cells according to your privileges.
Read More: VBA to Split String into Multiple Columns in Excel (2 Ways)
3. Split String into Cells Based on Different DelimitersÂ
Say, you have the dataset in the following manner.
Here, we put the footballers’ club where they spent successful time in their career and match and goal stats together. We separated them by SemiColon (;)and Slash (/) character/symbols. We will split all those strings into other cells in the following steps.
Steps:
- Follow Section 1 to open the VBA Module.
- Type the following code in the VBA Module.
Sub SplitClubMatchGoal() Dim rng As Range Set rng = Selection rng.TextToColumns _ Destination:=rng(1, 1).Offset(, 1), _ TextQualifier:=xlTextQualifierDoubleQuote, _ DataType:=xlDelimited, _ SemiColon:=True, _ Comma:=True, _ Other:=True, _ Space:=False, _ OtherChar:="/" End Sub
Code Explanation
- Here, we are calling our Sub Procedure as SplitClubMatchGoal and declare rng as Range.
- We want to split strings by selection, so we set rng to Selection.
- Then used the VBA TextToColumns method to split the strings and used VBA Offset to declare the destination to place the splitted strings.
- Finally, Our strings will be split according to different characters like commas, semicolons, slash , so we used Delimiter for them.
- Now go back to your sheet, select the range C4:C9 and run the Macro named SplitClubMatchGoal as it is your current Macro.
- The operation will split the major club, match and goal stats into cells beside column C.
Thus you can easily split strings into cells based on different characters. You can format those cells according to your privileges.
Read More: Excel Split Cell by Delimiter Formula
4. Excel VBA to Split String into Lower CellsÂ
Say, we want to see the Club names in lower cells. We need to split the strings of that column into lower cells to show the Club names separately. Let’s see how we can do this. (We will omit the Matches/Goals column for our convenience)
Steps:
- Follow Section 1 to open the VBA Module.
- Type the following code in the VBA Module.
Option Explicit
Sub SplitInLowerCells()
  Dim k As Long
  Dim Club() As String
  Dim Set_Row As Long
  With Worksheets("SplitLower")
    Set_Row = 5
    Do While True
      If .Cells(Set_Row, "C").Value = "" Then
        Exit Do
      End If
       Club = Split(.Cells(Set_Row, "C").Value, ",")
      If UBound(Club) > 0 Then
        .Cells(Set_Row, "C").Value = Club(0)
        For k = 1 To UBound(Club)
          Set_Row = Set_Row + 1
          .Rows(Set_Row).EntireRow.Insert
          .Cells(Set_Row, "C").Value = Club(k)
     .Cells(Set_Row, "D").Value = .Cells(Set_Row - 1, "D").Value
        Next
      End If
      Set_Row = Set_Row + 1
    Loop
  End With
 End Sub
Code Explanation
- Here, we named our Sub Procedure as SplitInLowerCells.
- Then, we declared our variable types.
- As the club information of the footballers begins from the 5th row, we set Set_Row to 5.
- After that, a VBA If Statement is provided within the Do While loop to check whether the cell is empty or not.
- Then, used the VBA Split to split the Strings and kept it in the Club
- Next, the IF condition is as follows: when Club is greater than 0 it will place the splitted values in the consecutive rows. Also used a For Loop to go through the selection.
- Now go back to your sheet and Run the Macro named SplitInLowerCells as it is your current Macro.
- After executing the command, the name of the Clubs will be split into the lower cells.
Thus you can easily split strings into lower cells by using the VBA Do While Loop.
Read More: Excel Formula to Split: 8 ExamplesÂ
Practice Section
In this section, I’m giving you the dataset that we worked on in this article so that you can practice these applications on your own.
Conclusion
The bottom line is, to split string into different cells by using Excel VBA is a very common thing to do when we work with Excel databases. And for this reason, it is important to understand the basic knowledge of VBA because using it properly is very efficient in this aspect. If you have any better ideas or feedback regarding this article, please feel free to share them in the comment box. This will help me enrich my upcoming articles.
Related Articles
- How to Split a Cell into Two Rows in Excel (3 ways)
- Excel VBA: Split String by Character (6 Useful Examples)
- How to Split Cells in Excel (5 Easy Tricks)
- How to Make Two Lines in One Cell in Excel (4 Methods)
- How to Split One Cell into Two in Excel (5 Useful Methods)
- How to Split Cells in Excel (The Ultimate Guide)
Hi, method ” Excel VBA to Split String into Lower Cells” is great for me, and I have question. what should the code look like so that, after dividing, there are no empty cells with the name of the player?
Hi Dav, thank you for reaching out. It’s unavoidable to have empty cells after the name of the players because we are splitting the adjacent cells to the Footballer Name column.