Consider this...

Dim X As Long
Dim BlockOfText As String
Dim Fields() As String
BlockOfText = "text1, text2, text3, etc."
'   The next statement assumes a comma followed
'    by a space, as shown, is the delimiting the text
Fields = Split(BlockOfText, ", ")
'    The zero-based Fields array now holds
'    each text sub-string in its elements...
For X = 0 To UBound(Fields)
  Debug.Print Fields(X)
Next

Rick

Here is some code you might use

Sub GetCSVData()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Const Delimiter = ","
Set fsread = CreateObject("Scripting.FileSystemObject")

'default folder
Folder = "C:\temp\test"
ChDir (Folder)

FName = Application.GetOpenFilename("CSV (*.csv),*.csv")

RowCount = LastRow + 1
If FName <> "" Then
     'open files
     Set fread = fsread.GetFile(FName)
     Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

     Do While tsread.atendofstream = False
     
        InputLine = tsread.ReadLine

        'extract comma seperated data
        ColumnCount = 1
        Do While InputLine <> ""
           DelimiterPosition = InStr(InputLine, Delimiter)
           If DelimiterPosition > 0 Then
              Data = Trim(Left(InputLine, DelimiterPosition - 1))
              InputLine = Mid(InputLine, DelimiterPosition + 1)
           Else
              Data = Trim(InputLine)
              InputLine = ""
           End If
           
           Cells(RowCount, ColumnCount) = Data
           ColumnCount = ColumnCount + 1
        Loop
        RowCount = RowCount + 1
     Loop
       
     tsread.Close
End If
End Sub