Use of Arrays for strings

just do this...

dim varArray as variant
dim strData as string
dim intCount as integer

strData = "aaa NNN aaa NNN aaa NNN"
varArray = split(strData," ") '<---split each item by the space
for iCount = lbound(varArray) to ubound(varArray)
  msgbox varArray(iCount)
next iCount


hope that helps
-brian

This will do EXACTLY what you want.



dim varArray as variant
dim strData as string
dim intCount as integer
dim strFinal as string

strData = "aaa NNN aaa NNN aaa NNN"
varArray = split(strData," ") '<---split each item by the space
for iCount = lbound(varArray) to ubound(varArray)
 strFinal = strFinal & "," & varArray(iCount)
next iCount
strFinal = right(strFinal,len(strFinal) - 1)
msgbox strFinal



-brian

You could use  Replace several times

Dim s As String, t As String

s = "n nn 'aaa aaa aaa aaa' 'aaaa aaa aa' nnn 'aaa aaa aaa'"
t = Replace(s, " '", "|'")
t = Replace(t, "' ", "'|")
t = Replace(t, "||", ",")
t = Replace(t, "|", ",")

BrianGeff719,

    Your code will break the strings apart.

         'aaa aaa aaa aaa'

    should stay together as string.


GrahamSkan,

    Your code was close but produced:

        n nn,'aaa aaa aaa aaa','aaaa aaa aa',nnn,'aaa aaa aaa'

    when it should be:

        n,nn,'aaa aaa aaa aaa','aaaa aaa aa',nnn,'aaa aaa aaa'

Here is one way to do it, stripping off the single quotes at the same time:

    Dim s As String, t As String
    Dim curChar As String
    Dim a As Integer, str As Boolean
    Dim values As Variant
   
    str = False
    s = "n nn 'aaa aaa aaa aaa' 'aaaa aaa aa' nnn 'aaa aaa aaa'"
    For a = 1 To Len(s)
        curChar = Mid(s, a, 1)
        If curChar = "'" Then
            str = Not str
            curChar = "" ' take this line out to leave single quotes on your strings
        ElseIf curChar = " " Then
            If Not str Then
                curChar = ","
            End If
        End If
        t = t & curChar
    Next a
   
    Debug.Print s
    Debug.Print t
   
    values = Split(t, ",")
    For a = 0 To UBound(values)
        Debug.Print values(a)
    Next a

Regards,

Idle_Mind

Idle_mind,
Your code gives the desired result but is it possible to get the same result using array command.

Regards
Nitin

Just use my code and add this to the for loop

if isNumeric(varArray(iCount)) = true then
 msgbox "array item " & icount & " is numeric"
end if

Idle_Minds code is using arrays.....do you see he uses the Split() command, split takes a string, and it will create an array based on a Delimiter.

VARIANT = ARRAY
So here is an example
RUN THIS CODE

dim values as variant
dim t as string
dim i as integer
t = "hello,my,name,is,brian"
values = Split(t, ",")
for i = lbound(values) to ubound(values)
msgbox values(i)
next i


Values is now an array created from the string (t) and each item is added to the array by commas.


I hope that helps you understand arrays.
-Brian

The desired strings end up in an array called "values" in my code.

Idle_Mind