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

Question

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:

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.

Worth pointing out that I am viewing the values within my array using 'add watch' in the excel VBA debug menu. Is it possible that my data is actually fine, but being displayed in an odd way by the debug console? — Petrichor
– Petrichor, Commented Dec 6, 2017 at 10:25
Let me guess: your text file is UTF-16 encoded. Your "Contact" is actually "C[NULL]O[NULL]...". Then you need to read the file using FileSystemObject and setting TriState=True... — LocEngineer
– LocEngineer, Commented Dec 6, 2017 at 14:31
@LocEngineer Yes! Thank you. On my workstation here I don't have Notepad++ so wasn't able to check had the thought even occurred to me. But I tried removing the ASCII null character from each array entry and that solved the issue. — Petrichor
– Petrichor, Commented Dec 6, 2017 at 16:04

Petrichor · Accepted Answer · 2017-12-07 12:22:26Z

1

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.

answered Dec 7, 2017 at 12:22

Petrichor

1,0851 gold badge12 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related