Excel VBA Code to split text by delimiters in multiple worksheets
September 12, 2017
Adam Dimech has posted a VBA code here: https://code.adonline.id.au/vba-text-to-columns/ which can be used to split text by any delimiter on multiple worksheets.
So you have two worksheets like this:


. . . you can edit the VBA code to split up the data. If you're delimiting by something other than a tab, semicolon, comma, or space, you can set the line beginning 'Other' to 'Other:=True', and leave the preceding four lines set to false. Enter the delimiter - in this example a pilcrow - ¶ - as shown below.

The data on worksheets will be automatically divided into columns.


Here's the full code
Sub Text2Columns()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
Select Case UCase(ws.Name)
Case "MASTER", "DATA"
'do nothing
Case Else
ws.Columns(1).TextToColumns Destination:=ws.Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=True, _
Comma:=False, _
Space:=False, _
Other:=False, OtherChar:="|", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
End Select
Next ws
Application.ScreenUpdating = True
End Sub