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