VB Text Array Split Delimiter results in unwanted lines

[Lil Stinker]

I'm using the following code to bring values into Excel that have been saved to a text file. I'm hitting two walls and hoping somebody could shed some light.

Problem 1: the original code as written uses vbNewLine as a delimiter which works fine to split the username from the new value... until it doesn't. It doesn't work when the new value in the text file happens to be broken into separate lines. With vbNewLine, it splits the new value after the first line and erases any data afterward.

Problem 2: so, I tried using a different delimiter in the split. The problem here is now I get an extra line before the new value when bringing the data into Excel so all of the cells appear blank because the value is actually on the next line in the cell. Even when the new value is broken into two lines, it brings in both lines of text underneath the unwanted line.

Is there a better way of handling splitting the username from the new value in the file while still allowing for multiline values?

Dim fileNAME As String, filePATH As String, fileTEXT As String, prevVAL As String, textARR() As String, newVAL As String
Dim shtNAME As String, cellADD As String, cellVAL As String, 
Dim userFLDR As String
Dim txtFILE As Integer

      Do While Len(fileNAME) > 0
         filePATH = userFLDR & fileNAME
         Open filePATH For Input As txtFILE
         fileTEXT = Input(LOF(txtFILE), txtFILE)
         Close txtFILE
         textARR = Split(fileTEXT, vbNewLine) ' "*o*") w/ different delimiter   *** adds extra line before value ***
         userNAME = textARR(0) 'user who made change
         newVAL = textARR(1) 'changed value
         Close #1
         
         shtNAME = Left(fileNAME, InStr(fileNAME, "--") - 1) 'extract sheet name
         cellADD = Replace(Right(fileNAME, Len(fileNAME) - InStr(fileNAME, "--") - 1), ".txt", "") 'cell address of change
         prevVAL = ThisWorkbook.Sheets(shtNAME).Range(cellADD).value 'set current value
         ThisWorkbook.Sheets(shtNAME).Range(cellADD).value = newVAL 'make cell change

         fileNAME = Dir() 'clear file name
      On Error Resume Next
         Kill (filePATH) 'delete file
      On Error GoTo 0
      Loop

 

[jimrward]

One approach might be as follows
Do as you are doing to pick up the username from split(0)
Next take your original string you read in and replace the username you have just found with a null
Finally take the resultant string and replace vbnewline with a null this should leave you with a text string