CODEWITHSUNDEEP
excelvba
siwanut jinnachit
siwanut jinnachit

Reputation: 3

Split string and delimiters into an array

I have the following string:

top,fen,test,delay,test

I want to convert it into an array in the following way:

{top}{,}{fen}{,}{delay}{,}{test}

Upvotes: 0

Views: 41020

Answers (3)

citizenkong
citizenkong

Reputation: 679

Here's a really short solution:

Function StringToCurlyArray(s As String) As String()

    StringToCurlyArray = Split("{" & Replace(s, ",", "}|{,}|{") & "}", "|")

End Function

Pass your comma-delimited string into that and you'll get an array of curly-braced strings back out, including commas.

Upvotes: 0

David Zemens
David Zemens

Reputation: 53663

If you actually need the commas as part of the array, then probably the simplest approach is to do a Replace statement, replacing each comma with a comma surrounded by some other character(s) you can use as a delimiter. Whatever character you opt to use should be unique enough that it is unlikely to appear in the rest of your word list. I will use an underscore, here, but you could use any other special character.

Sub test()
Dim wordlist As String
Dim arrayofWords
Dim i
wordlist = "top,fen,test,delay,test"
wordlist = Replace(wordlist, ",", "_,_")


arrayofWords = Split(wordlist, "_")

'Enclose each word in curly brackets
' omit if this part is not needed
For i = LBound(arrayofWords) To UBound(arrayofWords)
    arrayofWords(i) = "{" & arrayofWords(i) & "}"
Next

End Sub

You could use some funky double-byte character since these would rarely if ever be encountered in your word list, like so. 

Sub test()
Const oldDelimiter As String = ","
Dim splitter As String
Dim newDelimiter As String
Dim wordlist As String
Dim arrayofWords
Dim i As Long

'Create our new delimiter by concatenating a new string with the comma:
splitter = ChrW(&H25B2)
newDelimiter = splitter & oldDelimiter & splitter

'Define our word list:
wordlist = "top,fen,test,delay,test"

'Replace the comma with the new delimiter, defined above:
wordlist = Replace(wordlist, oldDelimiter, newDelimiter)

'Use SPLIT function to convert the string to an array
arrayofWords = Split(wordlist, splitter)

'Iterate the array and add curly brackets to each element
'Omit if this part is not needed
For i = LBound(arrayofWords) To UBound(arrayofWords)
    arrayofWords(i) = "{" & arrayofWords(i) & "}"
Next


End Sub

Here are results from the second method:

enter image description here

Upvotes: 5

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

Try something like this :

WordsList = Split("top,fen,test,delay,test", ",")

Result = ""
Count = UBound(WordsList)

For i = 0 To Count

    Result = Result  & "{" & WordsList(i) & "}"
    if i < Count then Result = Result & "{,}"

Next i

In an array will look like this :

WordsList = Split("top,fen,test,delay,test", ",")

Dim Result()
Count = (UBound(WordsList)*2) - 1
Redim Result(Count)
j = 0

For i = 0 To UBound(WordsList)

    Result(j) = WordsList(i)
    j = j + 1
    if j < Count then Result(j) = ","
    j = j + 1

Next i

Split : http://msdn.microsoft.com/en-us/library/6x627e5f%28v=vs.90%29.aspx

UBound : http://msdn.microsoft.com/en-us/library/95b8f22f%28v=vs.90%29.aspx

Redim : http://msdn.microsoft.com/en-us/library/w8k3cys2.aspx

Upvotes: 1

Related Questions