VBA code for "Text to Column - Fixed Width" - loop

Question

I am looking to find a way to make my code more simple, more efficient and create a loop that will be running until it reaches the last character.

First, I am getting data that always looks like the example below and should be pasted in a row (usually cell A1):

7666976-15012020092737.pdf; 7665725-15012020092757.pdf; 7669477-15012020092833.pdf; 7669483-15012020092844.pdf; 7669492-15012020092857.pdf; 7669494-15012020092910.pdf; 7669495-15012020092921.pdf; 7669546-15012020092933.pdf; 7669548-15012020092953.pdf; 7669548-15012020093010.pdf; 7669551-15012020093047.pdf; 7669552-15012020093111.pdf; 7669554-15012020093138.pdf; 7669557-15012020093205.pdf; 7669558-15012020093245.pdf; 7669563-15012020093311.pdf; 7672877-15012020093344.pdf; 7672879-15012020093401.pdf; 7672881-15012020093415.pdf; 7672882-15012020093425.pdf; 7672884-15012020093437.pdf

Then I am splitting it by using the Text to Column - Fixed Width option in Excel. The length of every fragment should be always 28 characters.

So far I managed to create the code below that is working, but I believe that it might be better - for example, if there are 1000 characters I should keep adding Array(728,1), Array (756,1), etc. to the code.

Range("A1").Select
    Selection.TextToColumns Destination:=Range("S1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(28, 1), Array(56, 1), Array(84, 1), Array(112, 1), _
        Array(140, 1), Array(168, 1), Array(196, 1), Array(224, 1), Array(252, 1), Array(280, 1), _
        Array(308, 1), Array(336, 1), Array(364, 1), Array(392, 1), Array(420, 1), Array(448, 1), _
        Array(476, 1), Array(504, 1), Array(532, 1), Array(560, 1), Array(588, 1), Array(616, 1), _
        Array(642, 1), Array(672, 1), Array(700, 1)), TrailingMinusNumbers:=True

How could it be improved?

Answer 1

You need to use both delimiters (i.e., semicolon & space).

Try this:

With ThisWorkbook.Sheets("TEST")
    .Cells(1).TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
        Semicolon:=True, Space:=True
    .Range("S1").CurrentRegion.Columns.AutoFit
End With

Answer 2

As mentioned in the comments, you might just use the Split function:

Sub Test()

Dim arr As Variant

With Sheet1 'Change according to your sheet's codename
    arr = Split(.Range("A1").Value, "; ")
    .Range("S1").Resize(, UBound(arr) + 1).Value = arr
End With

End Sub

Although Range.TextToColumns isn't bad either. Because you actually wanted to delimit by ; instead of just a semi-colon there were two* options:

• Remove the spaces/semicolons from your original string first:

    Sub Test
  
    With Sheet1 'Change according to your sheet's codename
        .Range("A1").Value = .Range("A1").Replace(" ", "")
        .Range("A1").TextToColumns .Range("S1"), DataType:=1, Semicolon:=True
    End With
  
    End Sub
  

• Use Application.Trim right after your code has run to remove leading/trailing spaces from all cells at once:

    Sub Test
  
    With Sheet1 'Change according to your sheet's codename
        .Range("A1").TextToColumns .Range("S1"), DataType:=1, Semicolon:=True
        Application.Trim (.Rows(1))
    End With
  
    End Sub
  

The latter one won't affect your A1 value, so you might want to go for that.

---

*Note: Setting ConsecutiveDelimiter to True makes for use of multiple delimiters as per EEM's answer.