CODEWITHSUNDEEP
arraysexcelvbasplit
Petrichor
Petrichor

Reputation: 1065

Split command in Excel VBA adding a space between every character in array

I am reading a text file and splitting the data string at each space using the following code. 

 L = f.ReadLine
        If L = "" Then GoTo errorpoint:
            On Error GoTo errorpoint:
            sl = Split(L, " ", -1)

However, the array I get back takes, for example, the word "Contact" in the text file and turns it into " C o n t a c t " in a single cell of my array (see below) and I'm not sure why.

Data Output in VBA Watch Window:

Data Output in VBA Watch Window

I have tried using 

For i = 0 To UBound(sl)
     sl(i) = Replace(sl(i), " ", "")
Next

Afterwards to remove the spaces, but that doesn't appear to be removing the spaces from my data. Any ideas how I can prevent the code from adding a space between every character in the first place?

I then need to check whether sl(1) and sl(2) contain the words "Contact" and "Stress" respectively, but that condition cannot be met currently, the cells contain " C o n t a c t " and " S t r e s s ", so I changed the if loop to reflect that, but the condition is still not met.

In addition, I cannot convert my data from String > Double because of the unusual formatting, it seems.

Upvotes: 0

Views: 645

Answers (1)

Petrichor
Petrichor

Reputation: 1065

As @LocEngineer pointed out in the comments, during the update of the external software that produces the text file I was reading, the text encoding had been changed to UTF-16 and the additional characters between my data were chr(0) Null values from the ASCII table, not spaces (chr(32) in ASCII). 

For i = 0 To UBound(sl)
     sl(i) = Replace(sl(i), Chr(0), "")
Next

This snippet solved the problem.

Upvotes: 1

Related Questions