[RESOLVED] String Replacement ?

[rocker123]

i have a string. let say

strMyString = "Array(,Rectangle,Oval,Circle,,)"

and i want my final result something like this

strMyString = "Array("Rectangle","Oval","Circle")"

i struggled a few days or so in VBA with many functions such as mid,instr,instrrev,loop. i know the problems are the characters before and after this mark "," (the comma ) however i'm still stuck.Can any of you can do this. Meanwhile i will do my best before check out this post again. any help is appreciated.

[rocker123]

for simpler string such as strMyString = "Array(,,O,R,,)". i nearly did it with the following code just a little bit wrong logic somewhere in the first character however i think i will fine-tune it with no problem after a short sleep ( now i feel quite a lot more relax and happy with my effort so far ). if you guys have better solution just post it here for everyone reference.

Public Sub testcode()
    Dim strMyString, strChar1, strChar2, strChar3, strChar4, strChar5, strChar6, strChar7, strChar8, strChar9, strChar10 As String
    strMyString = "Array(,,O,R,,)" 'final result will look like this "Array("Oval","Rec")"
    
    strChar1 = Switch(Mid(strMyString, 7, 1) <> ",", Mid(strMyString, 7, 1), Mid(strMyString, 7, 1) = "," And _
    Mid(strMyString, 6, 1) = "(", """")
    
    strChar2 = Switch(Mid(strMyString, 8, 1) <> ",", Mid(strMyString, 8, 1), Mid(strMyString, 8, 1) = "," And _
    Mid(strMyString, 7, 1) = "(", """", Mid(strMyString, 8, 1) = "," And Mid(strMyString, 7, 1) = """", "", _
    Mid(strMyString, 8, 1) = "," And Mid(strMyString, 7, 1) <> """" And Mid(strMyString, 7, 1) <> "(" And _
    Mid(strMyString, 9, 1) <> ")" And Mid(strMyString, 9, 1) <> ",", """,""", Mid(strMyString, 8, 1) = "," And _
    Mid(strMyString, 9, 1) = "," And Mid(strMyString, 7, 1) <> ",", "", Mid(strMyString, 8, 1) = "," And Mid(strMyString, 9, 1) = ")", """")
    
    strChar3 = Switch(Mid(strMyString, 9, 1) <> ",", Mid(strMyString, 9, 1), Mid(strMyString, 9, 1) = "," And _
    Mid(strMyString, 8, 1) = "(", """", Mid(strMyString, 9, 1) = "," And Mid(strMyString, 8, 1) = """", "", _
    Mid(strMyString, 9, 1) = "," And Mid(strMyString, 8, 1) <> """" And Mid(strMyString, 8, 1) <> "(" And _
    Mid(strMyString, 10, 1) <> ")" And Mid(strMyString, 10, 1) <> ",", """,""", Mid(strMyString, 9, 1) = "," And _
    Mid(strMyString, 10, 1) = "," And Mid(strMyString, 8, 1) <> ",", "", Mid(strMyString, 9, 1) = "," And Mid(strMyString, 10, 1) = ")", """")
    
    strChar4 = Switch(Mid(strMyString, 10, 1) <> ",", Mid(strMyString, 10, 1), Mid(strMyString, 10, 1) = "," And _
    Mid(strMyString, 9, 1) = "(", """", Mid(strMyString, 10, 1) = "," And Mid(strMyString, 9, 1) = """", "", _
    Mid(strMyString, 10, 1) = "," And Mid(strMyString, 9, 1) <> """" And Mid(strMyString, 9, 1) <> "(" And _
    Mid(strMyString, 11, 1) <> ")" And Mid(strMyString, 11, 1) <> ",", """,""", Mid(strMyString, 10, 1) = "," And _
    Mid(strMyString, 11, 1) = "," And Mid(strMyString, 9, 1) <> ",", "", Mid(strMyString, 10, 1) = "," And Mid(strMyString, 11, 1) = ")", """")
    
    strChar5 = Switch(Mid(strMyString, 11, 1) <> ",", Mid(strMyString, 11, 1), Mid(strMyString, 11, 1) = "," And _
    Mid(strMyString, 10, 1) = "(", """", Mid(strMyString, 11, 1) = "," And Mid(strMyString, 10, 1) = """", "", _
    Mid(strMyString, 11, 1) = "," And Mid(strMyString, 10, 1) <> """" And Mid(strMyString, 10, 1) <> "(" And _
    Mid(strMyString, 13, 1) <> ")" And Mid(strMyString, 13, 1) <> ",", """,""", Mid(strMyString, 11, 1) = "," And _
    Mid(strMyString, 13, 1) = "," And Mid(strMyString, 10, 1) <> ",", "", Mid(strMyString, 11, 1) = "," And Mid(strMyString, 13, 1) = ")", """")
    
    strChar6 = Switch(Mid(strMyString, 12, 1) <> ",", Mid(strMyString, 12, 1), Mid(strMyString, 12, 1) = "," And _
    Mid(strMyString, 11, 1) = "(", """", Mid(strMyString, 12, 1) = "," And Mid(strMyString, 11, 1) = """", "", _
    Mid(strMyString, 12, 1) = "," And Mid(strMyString, 11, 1) <> """" And Mid(strMyString, 11, 1) <> "(" And _
    Mid(strMyString, 13, 1) <> ")" And Mid(strMyString, 13, 1) <> ",", """,""", Mid(strMyString, 12, 1) = "," And _
    Mid(strMyString, 13, 1) = "," And Mid(strMyString, 11, 1) <> ",", "", Mid(strMyString, 12, 1) = "," And Mid(strMyString, 13, 1) = ")", """")
    
    strChar7 = Switch(Mid(strMyString, 13, 1) <> ",", Mid(strMyString, 13, 1), Mid(strMyString, 13, 1) = "," And _
    Mid(strMyString, 12, 1) = "(", """", Mid(strMyString, 13, 1) = "," And Mid(strMyString, 12, 1) = """", "", _
    Mid(strMyString, 13, 1) = "," And Mid(strMyString, 12, 1) <> """" And Mid(strMyString, 12, 1) <> "(" And _
    Mid(strMyString, 14, 1) <> ")" And Mid(strMyString, 14, 1) <> ",", """,""", Mid(strMyString, 13, 1) = "," And _
    Mid(strMyString, 14, 1) = "," And Mid(strMyString, 12, 1) <> ",", "", Mid(strMyString, 13, 1) = "," And Mid(strMyString, 14, 1) = ")", """")
    
    MsgBox "Array(" & strChar1 & strChar2 & strChar3 & strChar4 & strChar5 & strChar6 & strChar7 & ")"
End Sub

PS: as i said, the problems lie in the mark "," ( the comma ). the characters after it and before it and what happens after the first replacement occurs. i believe there must be some better ways beyond my knowledge at the time being and you guys the professionals can demonstrate it.if so do it please, it can be beautiful, thanks again

[seenu_1st]

try like this

Dim StrMyString As String, MyStr As String, StrResult As String
Dim StrSplit, i  As Long

StrMyString = "Array(,,O,R,,)"
StrSplit = Split(StrMyString, ",")
For i = LBound(StrSplit) + 1 To UBound(StrSplit) - 1
    If StrSplit(i) <> "" Then
        Select Case StrSplit(i)
            Case "O"
                MyStr = "Ovel"
            Case "R"
                MyStr = "Rec"
            'add more case as u need
        End Select
        If StrResult = "" Then
            StrResult = MyStr
        Else
            StrResult = StrResult & "," & MyStr
        End If
    End If
Next
StrResult = "Array(" & StrResult & ")"

[westconn1]

you can try this to see if it will work for you

s = "Array(,Rectangle,Oval,Circle,,)"
s = Replace(Replace(Replace(Replace(Replace(s, ",,", ""), "(,", "("), "(", "("""), ")", """)"), ",", """,""")
msgbox s

[rocker123]

thanks seenu_1st. i actually thought of something like you but give up because i don't know there is a function called split

thanks westconn1 again, your code is gorgeous, simple , complicated and so attractive. i will rate your post again immediately after i gain some reputation.