Array version of texttocolumn?

[Jaymond Flurrie]

So, I have a string that says

-enter--tab-somerandomletters-tab--enter- (those tabs and enters are t-a-b and e-n-t-e-r not actual tabs and enters). Now, if I put this to a cell A1, I can use TextToColumns like

range("a1").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, Other:=True, OtherChar:="-"

which makes it five different cells (B1:F1).

How about splitting it to a five element array? Is there any built-in function for that?

 

[pgc01]

Hi Jaymond

Use Split(). Ex.:

Dim arr
 
arr = Split(Range("A1"), "-")

 

[Jaymond Flurrie]

This goes a bit to off-topic, but how is that array "arr" and not "arr()"? Why I can't use parentheses there? Doesn't array always need parentheses?

 

[pgc01]

I used an untyped declaration for arr. If you do not specify the type then excel declares the variable as Variant. A Variant can contain anything, a built-in type value, an array, an object. In this case the Variant will contain the result of the Split, which is an array of strings.

You could use a typed declaration, in this case an array of strings:

Dim sArr() As String
 
sArr = Split(Range("A1"), "-")

I'm glad you posted this, as I prefer this typed declaration.