Use of Arrays for strings

I had never used Arrays and wanted to know how to use Arrays for strings.
I am trying to read file by each line and then split using array.
The strings are like
n nn 'aaa' nnn
or
n nn 'aaa aaa aaa aaa' 'aaaa aaa aa' nnn 'aaa aaa aaa'
Where 'n' is numbers and 'a' is alphabets

Presently I am using Replace function to get

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

But I want the string to be

n,nn,'aaa',nnn......(NO CHANGE can use Replace function)
and
n,nn,'aaa aaa aaa aaa','aaaa aaa aa',nnn,'aaa aaa aaa'

Regards
Nitin
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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
Commented:

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

RetiredCommented:
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, "|", ",")
High School Computer Science, Computer Applications, and Mathematics TeachersCommented:
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
Author Commented:
Idle_mind,
Your code gives the desired result but is it possible to get the same result using array command.

Regards
Nitin
Commented:
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
Commented:
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
High School Computer Science, Computer Applications, and Mathematics TeachersCommented:
The desired strings end up in an array called "values" in my code.

Idle_Mind
High School Computer Science, Computer Applications, and Mathematics TeachersCommented:
Here is the snippet of code that actually produces the array:

values = Split(t, ",") ' <----- Array generated here
For a = 0 To UBound(values)
Debug.Print values(a)
Next a

The For...Next loop is there just to demonstrate that the array was actually generated and to show its contents.

Idle_Mind

Experts Exchange Solution brought to you by