Kapat

Split Cell Content Based On Inputbox Choice

excel  vba sample VBA Split Example

It may be necessary to split a long string of text into parts by delimiter or by a character (, . – space etc.). For this, VBA Split function helps us.

We have added an inputbox so that the string can be split into parts based on the user choise. When the button on the worksheet is clicked, an inputbox is opened. The contents of cell D1 are split into parts according to the delimiter or character that user entered in the inputbox.. The result of split function is listed, starting in cell A2. The number of split values is displayed to the user on a msgbox.

excel split
vba split function

VBA codes to split string : excel vba split

...
Dim ayir, pir As Variant, cleartxt As Long
cleartxt = Range("A" & Rows.Count).End(xlDown).Row
Range("A2:A" & cleartxt).ClearContents

pir = Application.InputBox("Which delimiter do you want to use for splitting ?" & vbCrLf & "" & vbCrLf & "For example :  , ; . - b 3 etc. or you can leave space", "", ",")
If pir = False Then
Exit Sub
End If
 
    ayir = Split(Range("D1").Value, pir)
    If pir = "" Then
         ayir = Split(Range("D1").Value, " ")
         End If
    On Error Resume Next
    For i = 0 To UBound(ayir)
        Range("A" & Rows.Count).End(xlUp)(2, 1) = ayir(i)
       
    Next i
    MsgBox "The Splitted Data :" & UBound(ayir), , ""
...

📥 Download sample workbook


Split Cell With Multiple Lines Into Rows

To split multi-line cells into rows, we created a different macro in a separate template.

vba split cell with multi lines into rows
Private Sub CommandButton2_Click()
Dim ayir As Variant, cleartxt As Long
cleartxt = Range("A" & Rows.Count).End(xlDown).Row
Range("A2:A" & cleartxt).ClearContents

 ayir = Split(Range("D1").Value, Chr(10))

 On Error Resume Next
 For i = 0 To UBound(ayir)
    Range("A" & Rows.Count).End(xlUp)(2, 1) = ayir(i)
 
Next i
    MsgBox "The Splitted Data :" & UBound(ayir) + 1, , ""
End Sub

📥 Download sample workbook