vba text to column delimiter not working

[GlennW81]

I have a line of code for text to column with comma as the delimiter.

ws.Columns(1).TextToColumns Destination:=ws.Range("A1"), DataType:=xlDelimited, comma:=True

A month ago this was working as expected. Although now it also appears to be including space as a delimiter.
For example, when it was working the following text:

Tuesday 23 05 2023 07_29_01 AM_AM_NF_LeftRear

7:46:57 AM,AM,NF,LeftRear,638203877138070000,

would be converted to:

Tuesday 23 05 2023 07_29_01 AM_AM_NF_LeftRear
7:46:57	AM	NF	6.38E+17

Although now it appears to be also including space as a delimiter and is converting it to:

Tuesday	23​	5​	2023​	07_29_01 AM_AM_NF_LeftRear
7:46:57	AM	AM	NF	LeftRear	6.38E+17​

Why is it all suddenly including space as a delimiter? has there been any updates which would default the space delimiter to True?
Should I also include space:=false in the code?

Regards
Glenn

 

[DanteAmor]

Try

  ws.Range("A:A").TextToColumns Destination:=ws.Range("A1"), DataType:=xlDelimited, Comma:=True, Space:=False

 

[GlennW81]

Thanks Dante,

This worked. I suspected it may have been a simple addition.
It seems odd that the code started behaving differently. It might be possible that recent excel updates etc. changed the default status for Space or ConsecutiveDelimiter, resulting in the need to add space:=False.

Cheers

 

[Rick Rothstein]

It seems odd that the code started behaving differently

You have to be careful with TextToColumns because any filters that were set during the current session remain active until either the session ends or the filter is modified. This applies whether the filter was set via VBA code or via the Text To Columns dialog box. So, if you did a text to columns that involved the space character as a delimiter sometime during the current session, it will still be an active filter when you do your next TextToColumns unless you deliberately set it to False. When I do TextToColumns in code, I set each filter within the code to the value I want/need it to be.