VBA Split function

[Jurgus]

Hi,

I need your help once more

I have data in csv:

word1,"word2 "word2" "word2" word2","word3","word4","word5","word6"

that I import to different excel sheet so I'm getting

"word1,""word2 ""word2"" ""word2"" word2"",""word3"",""word4"",""word5"",""word6"""

after using split

Split(value, ",")

I'm getting

"word1

""word2 ""word2"" ""word2"" word2""

""word3""

""word4""

""word5""

""word6"""

My final goal is:

word1

word2 "word2" "word2" word2

word3

word4

word5

word6

any ideas?

Thanks,

 

[rlv01]

Your explanation is confusing, so I am not clear about what you are trying to do.

Book2
	A	B	C	D	E	F
1	"word1,"word2 "word2" "word2" word2","word3","word4","word5","word6"
2	word1	word2 "word2" "word2" word2	word3	word4	word5	word6
Sheet4

Sub Test()
    Dim S As String, I As Long
    Dim SA As Variant, SB As Variant

    SA = Split(Replace(Range("A1").Value, Chr(34), ""), ",")
    SB = Split(SA(1), " ")
    
    For I = 0 To UBound(SB)
        Select Case I
        Case UBound(SB)
            S = S & SB(I) & " "
        Case Is > 0
            S = S & Chr(34) & SB(I) & Chr(34) & " "
        Case Else
            S = S & SB(I) & " "
        End Select
    Next I

    SA(1) = Trim(S)
    
    With Range("A2")
        .Resize(1, UBound(SA) + 1).Value = SA
    End With
End Sub

 

[Jurgus]

Your explanation is confusing, so I am not clear about what you are trying to do.

Book2
	A	B	C	D	E	F
1	"word1,"word2 "word2" "word2" word2","word3","word4","word5","word6"
2	word1	word2 "word2" "word2" word2	word3	word4	word5	word6
Sheet4

Sub Test()
    Dim S As String, I As Long
    Dim SA As Variant, SB As Variant

    SA = Split(Replace(Range("A1").Value, Chr(34), ""), ",")
    SB = Split(SA(1), " ")
   
    For I = 0 To UBound(SB)
        Select Case I
        Case UBound(SB)
            S = S & SB(I) & " "
        Case Is > 0
            S = S & Chr(34) & SB(I) & Chr(34) & " "
        Case Else
            S = S & SB(I) & " "
        End Select
    Next I

    SA(1) = Trim(S)
   
    With Range("A2")
        .Resize(1, UBound(SA) + 1).Value = SA
    End With
End Sub

sorry for bad explanation, when I'm reading it now it can be really confusing :P but it works perfect, thank you