Split string into array

[gauntletxg]

Say I have a string, "a test array." I want to split this into an array where each character is an element. I was thinking something like

Dim MyArray As Variant
Dim MyString As String
MyString = "a test array"
MyArray = Split(MyString, "", Len(MyString), vbTextCompare)

But this doesn't work because Split() returns the entire string when the delimiter is a zero-length string. How would I go about doing this?

Thanks.

 

[Norie]

Say I have a string, "a test array." I want to split this into an array where each character is an element. I was thinking something like

Dim MyArray As Variant
Dim MyString As String
MyString = "a test array"
MyArray = Split(MyString, "", Len(MyString), vbTextCompare)

But this doesn't work because Split() returns the entire string when the delimiter is a zero-length string. How would I go about doing this?

Thanks.

Perhaps I'm missing something but is the delimiter a zero-length string?

Isn't it " "?

 

[Scott Huish]

No, it wouldn't be " ", the op doesn't to split words, he wants to split by character, in other words from the string "a test array"

This should return an array of the following
a
(space)
t
e
s
t
(space)
a
r
r
a
y

 

[Norie]

HOTPEPPER

Yeah, kind of realised that after a reread.

Mind you seems like a strange request, to me anyway.

 

[mikerickson]

Range("A1").Characters might serve the OP's need.

 

[Norie]

Might also be worth taking a look into regular expressions.

 

[pgc01]

Nice function, Fencliff.

Some observations:

1 - It seems the function returns 1 element too much?

Maybe:

Public Function StringToCharArray(ByRef sIn As String) As String()
    StringToCharArray = Split(StrConv(sIn, vbUnicode), Chr(0))
    If UBound(StringToCharArray) > 0 Then _
        ReDim Preserve StringToCharArray(UBound(StringToCharArray) - 1)
End Function

2 - It will work for ansi characters but not for Unicode characters. If you have a really Unicode output maybe you really have to loop

 

[gauntletxg]

That is pretty cool. Thanks everyone.

 

[xenou]

Nice formula Fencliff. Any idea why Unicode strings are treated differrently by Split() in Vba? Or, rather, how in the world did you know that...

One small detail - the array returned has a vbNull string ending the array (which I guess makes it like a C character sequence). Maybe in a vba routine you'd need to ignore this last element in certain types of loops.

--AB

 

[Richard Schollar]

Nice formula Fencliff. Any idea why Unicode strings are treated differrently by Split() in Vba? Or, rather, how in the world did you know that...

One small detail - the array returned has a vbNull string ending the array (which I guess makes it like a C character sequence). Maybe in a vba routine you'd need to ignore this last element in certain types of loops.

--AB

VBA internally stores strings in unicode - the strconv function using the arguments as specified outputs a byte array of each character (2 bytes per character as per unicode) separated by a Chr(0). You can assign byte arrays to strings and strings to byte arrays in VBA using straightforward variable assignment eg:

Dim b() As Byte
Dim strSomeString As String

strSomeString = "This is a string"

'assign to byte array:

b = strSomeString

'check output:

for i = LBound(b) to UBound(b)
  Debug.Print b(i)
Next

'convert back:

strSomeOtherString = b

Msgbox strSomeOtherString

 

[gauntletxg]

Might also be worth taking a look into regular expressions.

Yeah, I've tried. RegEx makes my brain hurt. :/

Mind you seems like a strange request, to me anyway.

Basically, there's a sheet in Excel that we have to use at work. One part of it consists of 30, very narrow columns, and we have to put a letter into each cell (this sheet is printed and used as backup for journal entries - the 30 character limit is because that is the limit in JD Edwards for journal entry descriptions). A redesign is out of the question, so I'm working on a workaround, where you click a button which opens a userform/input box (haven't decided yet) with a single text box, you enter your description there, and then each character goes into the respective cell. Much easier than keying the description in letter by letter.