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.
VBA codes to split string :
... 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), , "" ...