Splitting Strings Into Multiple Rows With VBA

Excel Split String Based On User Choice


           When entering data in cell D1, the wrap-text function works and cell D1's height is set .Cell D1's width not change .    
            If the button in page is pressed inputbox opens.You can write in the inputbox, which delimiter will be used for splitting .
The results are listed in  column A. The splitted data total number are displayed on msgbox.


The codes that we used to split data:
Sub Bol2()
Dim ayir As Variant, pir As Variant
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 & "Example : , ; . b n 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 "Splitted Data Number :" & UBound(ayir)
End Sub

No comments:

Post a Comment