How to Split text and loop to next row

Question

I'm using the split function to split text using spaces. I have gotten my macro to split the text but I am having difficulties getting the loop to move to the next row below to split.

Sub Split_Text_Test1()

Dim Txt As String
Dim i As Integer
Dim FullName As Variant

Range("A1").Select

Txt = ActiveCell.Value

FullName = Split(Txt, " ")

For i = 0 To UBound(FullName)
    Cells(1, i + 1).Value = FullName(i)
Next i

End Sub

SJR · Answer 1 · 2019-02-06T18:32:13.673

1

You probably need to change the bit inside your loop thus as you are starting at A1. This assumes you want the entries in A2 and down. Not generally advisable to use Select/Activate, not very robust.

Edited to move across columns rather than down rows.

For i = 0 To UBound(FullName)
    Range("A1").Offset(,i + 1).Value = FullName(i)
Next i

That said, you can avoid a loop altogether and use

Range("B1").Resize(, UBound(FullName) + 1).Value = FullName

edited Feb 06 '19 at 18:32

answered Feb 06 '19 at 17:45

SJR

22,986
6
18
26

Hi SJR, thanks for responding to my question. When I run this it is split my text into different rows. Ideally, I would like the column A text to be split into columns A, B and C and then for the macro to go to the next row to do the same thing. – Brandon M. Feb 06 '19 at 18:05
Just put a comma in front of the Offset (amended above) to offset column rather than row. However, why don't you use Text to Columns, much more efficient? – SJR Feb 06 '19 at 18:07
SJR can you explain what you mean Text to Columns? Do you mean formulas in the columns? – Brandon M. Feb 06 '19 at 18:28
No, it's an option in the Data menu. A few clicks and you're done https://www.excel-easy.com/examples/text-to-columns.html – SJR Feb 06 '19 at 18:32
For a little more background - I created code that opens a text file and copies data from that text file and pastes it into cell A1 in this workbook every time I run it. I just used the Text to Columns function and it works as I would like but when I run my macro to pull in new data the Text to Columns clears out. – Brandon M. Feb 06 '19 at 18:45

score 0 · Answer 2 · answered Feb 06 '19 at 18:00

0

Sub Split_Text_Test1()

Dim Txt As String
Dim i As Integer
Dim FullName As Variant
Dim R As Integer, C As Integer
Range("A1").Select ' assumes that the cells below that are empty 

Txt = ActiveCell.Value

FullName = Split(Txt, " ")
R = ActiveCell.Row
C = ActiveCell.Column
For i = 0 To UBound(FullName)
    Cells(R + 1 + i, C).Value = FullName(i)
Next i

End Sub

answered Feb 06 '19 at 18:00

Wilhelm

196
10

Hi Wilhelm, thanks for responding to my question. When I run this it is split my text into different rows. Ideally, I would like the column A text to be split into columns A, B and C and then for the macro to go to the next row to do the same thing. – Brandon M. Feb 06 '19 at 18:05
Also, the text that I am splitting all looks similar to this "14912E33 190205 099.7920 ". It has 55 spaces at the end which I think is also throwing my code off. I have a macro that copies this text from a .TXT file which is why it looks goofy. – Brandon M. Feb 06 '19 at 18:08

score 0 · Accepted Answer · answered Feb 06 '19 at 18:42

0

In this case I would use a loop (and your solution was not that far from this):

Dim Txt As String
Dim i As Integer
Dim FullName As Variant
Dim R As Integer, C As Integer, MaxR as Integer
C = 1 ' can be another loop as well
For R = 1 to 1000

Txt = Trim(Cells(r,1).Value) ' will remove space from start and end
FullName = Split(Txt, " ")

For i = 0 To UBound(FullName)
    Cells(R , C + 1 + i ).Value = FullName(i)
Next i

Next R

answered Feb 06 '19 at 18:42

Wilhelm

196
10

Note that row couting variables should always be of type `Long` because Excel has more rows than `Integer` can handle `Dim R As Long`. I recommend [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. • `MaxR` is declared but never used. – Pᴇʜ Feb 07 '19 at 07:16

score 0 · Answer 4 · answered Feb 06 '19 at 18:58

I added few thing to your code, see if that serves your purpose. However, as SJR said Text to columns option in Data menu would do the same with less effort.

Sub Split_Text_Test1()

Dim Txt As String
Dim i As Integer
Dim FullName As Variant
Dim lastRow As Long
Dim myRange As Range

With ActiveSheet.UsedRange
        lastRow = .Rows(.Rows.Count).Row
End With
Debug.Print lastRow

'Range("A1").Select

Set myRange = ActiveSheet.Range("A1", "A" & lastRow)

    For Each cell In myRange

        Txt = cell.Value

        FullName = Split(Txt, " ")

            For i = 0 To UBound(FullName)
                Cells(cell.Row, i + 1).Value = FullName(i)
            Next i
    Next cell

End Sub

How to Split text and loop to next row

4 Answers4