top of page

Excel VBA Code to split text by delimiters in multiple worksheets

Adam Dimech has posted a VBA code here: 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

bottom of page