Excel split string loop

Question

I have 4 different ranges. I need to split the contents of these ranges. so the cell contains; Tom Jerry Bugs Bunny Jack Sparrow Bob. And I want to split that into 7 different cells. I have looked on the internet and have discover how to perform a split like this with the following code.

Dim text As String
Dim a As Integer
Dim name As Variant
text = ActiveCell.Value
name = Split(text, " ")
For a = 0 To UBound(name)
Cells(1, a + 1).Value = name(a)
Next a

My issue is i need this inside a loop so it will go through a range, say ("H1:H200") and this is were I am stuck, I have started the loop, but cannot get the split to work inside it. Here is the start of my loop:

Dim rCell As Range
Dim rRng As Range

Set rRng = Range("H1:H200")
For Each rCell In rRng.Cells
 'split string here.

Any help on this issue would be appreciated! Thanks.

Jean-François Corbett · Accepted Answer · 2014-09-10 10:04:31Z

1

Why reinvent the wheel? You could just use TextToColumns. For example:

Dim rCell As Range
Dim rRng As Range

Set rRng = Range("H1:H200")
For Each rCell In rRng.Cells
    rCell.TextToColumns Destination:=rCell.Offset(0, 1), Space:=True
Next

answered Sep 10, 2014 at 10:04

Jean-François Corbett

38.7k30 gold badges144 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PaulFrancis · Accepted Answer · 2014-09-10 10:06:45Z

1

You could use the For inside the For. Something like,

Dim txtStr As String
Dim aCtr As Integer
Dim nameVar() As String
Dim rCell As Range
Dim rRng As Range

Set rRng = Range("H1:H200")
For Each rCell In rRng.Cells
    txtStr = rCell.Value
    nameVar = Split(txtStr, " ")
    For aCtr = 0 To UBound(nameVar)
        Cells(1, aCtr + 1).Value = nameVar(aCtr)
    Next
Next

answered Sep 10, 2014 at 10:06

PaulFrancis

5,8191 gold badge23 silver badges38 bronze badges

Comments

Lancealott92 · Accepted Answer · 2014-09-10 12:20:37Z

0

Came to this conclusion in the end on my own, thanks for the answers though

Dim bb As Integer
Dim text As String
Dim aa As Integer
Dim name As Variant
Dim cc As Range
Dim rng As Range
bb = 2
Set rng = Range("H2:H200")
For Each cc In rng
 text = cc.Value
 name = Split(text, " ")
 For aa = 0 To UBound(name)
 'Ubound finds the end of an array
 Cells(bb, aa + 1).Value = name(aa)
Next aa

bb = bb + 1 Next

answered Sep 10, 2014 at 12:20

Lancealott92

854 silver badges12 bronze badges

Collectives™ on Stack Overflow

Excel split string loop

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related