VBA - Excel : Split string in a different column

Question

I want the user of my excel file to enter a sentence in the cell "B2" and then have a Sub to parse the sentence in a different column (from D2 to Dn). So for example, if you type "aaa bbb ccc ddd" in B2, you should have as a result :
D2 : aaa
D3 : bbb
D4 : ccc
D5 : ddd

I found how to split the sentence with VBA using the split function, but I have a hard time populating the column D as I don't know how to define the last row (Dn). Here is what I am using so far :

Sub splitAddress()
Dim strAddress As String

strAddress = Range("B2").Value
Range("D2:D9").Value = WorksheetFunction.Transpose(Split(strAddress, " "))

End Sub

I want to modify the "D2:D9" as D9 isn't always gonna be the last row of the column. How to write that it should populate from D2 to Dn according to the number of words in my B2 cell? Thanks in advance !

Range("D2").Resize(Ubound(arr)+1,1).Value = WorksheetFunction.Transpose(arr) where arr is the array created using Split() — Tim Williams
– Tim Williams, Commented Apr 22, 2013 at 2:58

Glenn Stevens · Accepted Answer · 2013-04-22 02:58:24Z

4

There are probably more elegant ways to do this, but if you split the address into an array you can get the number of elements in the array using Ubound and use .Resize to increase the number of rows in your range:

Sub splitAddress()
  Dim strAddress As String
  Dim strAddressParts() As String
  Dim numParts As Integer

  strAddress = Range("B2").Value

  strAddressParts = Split(strAddress, " ")
  numParts = UBound(strAddressParts) + 1

  Range("D2").Resize(numParts).Value = WorksheetFunction.Transpose(strAddressParts)
End Sub

answered Apr 22, 2013 at 2:58

Glenn Stevens

1,99814 silver badges21 bronze badges

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

1 Comment

Phalanx Over a year ago

Thanks @Glenn Stevens, clear and easy to use, perfect for what I'm doing !

Simon1979 · Accepted Answer · 2013-04-22 02:59:37Z

2

a loop like below would do it for you:

Sub splitAddress()

Dim i As Integer, x As Integer
Dim c As Range

i = 0
x = 1

Set c = Range("A5")

i = InStr(c.Value, " ")

c.Offset(0, x).Value = Left(c.Value, i - 1)
x = x + 1
i = InStr(i, c.Value, " ")

Do Until InStr(i + 1, c.Value, " ") = 0

    c.Offset(0, x).Value = Mid(c.Value, i + 1, InStr(i + 1, c.Value, " ") - i)
    i = InStr(i + 1, c.Value, " ")
    x = x + 1
Loop

End Sub

answered Apr 22, 2013 at 2:59

Simon1979

2,1081 gold badge12 silver badges15 bronze badges

3 Comments

Phalanx Over a year ago

Thanks for your answer, it's more complicated than Glenn Stevens' answer but it's an interesting one anyway!

Simon1979 Over a year ago

You're welcome, I tend to use loops like this as it gives ability to further add criteria to what you are doing - check for additional (double) spaces etc. Possibly overkill for what you're doing but may answer someone else's problem.

Phalanx Over a year ago

Oh yeah right, will remember this if I eventually have to add more criterias to my program !

Collectives™ on Stack Overflow

VBA - Excel : Split string in a different column

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related