TextToColumns Method - Delimited by Double Spaces

[Alan]

Hi All,

I am having trouble getting the TextToColumns method of the range
class to work as I need.

The code I am using simplifies to the following:

Sub TTC()

Range("A1").TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:=Chr(32) & Chr(32), _
FieldInfo:=Array(Array(1, 1), Array(2, 1))

End Sub


The problem is that I cannot get it to recognise two spaces as the
delimiter (as opposed to a single space).

I have tried replacing two spaces (" ") with the expression:

Chr(32) & Chr(32)

but that still doesn't seem to work.

If I put "a b" (="a" & chr(32) & "b") into A1 and run that code, it
still splits the two letters into A1 and B1.

The data set is of a form where the delimiter is two spaces, but a
single space legitimately arises within a field ("FirstName LastName")
would be an example.

Can anyone advise on what I am doing wrong? It seems to be something
that should be quite a simple and commonplace requirement, so perhaps
I am just having a case of short-sightedness?

Thanks,

Alan.

 

[Norman Jones]

Hi Alan,

With the 'Treat consecutive delimiters as one" selected in Excel

or (in VBA)

ConsecutiveDelimiter:=True,

set the delimiter to " " (Chr(32)) and both spaces will be teated as a
single space.

In short, set the delimiter to a single space chr(32)) and all should be
well!

 

[Alan]

Hi Alan,

With the 'Treat consecutive delimiters as one" selected in Excel

or (in VBA)

ConsecutiveDelimiter:=True,

set the delimiter to " " (Chr(32)) and both spaces will be teated
as a single space.

In short, set the delimiter to a single space chr(32)) and all
should be well!

Hi Norman,

If I do that, it will split the "FirstName LastName" field into two
cells though.

Or am I misunderstanding your suggestion?

Thanks,

Alan.

 

[Norman Jones]

Hi Alan,

I mis-read.

Try:

Sub Tester()

Selection.Replace What:=" ", _
Replacement:="#", _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
MatchCase:=False

Selection.TextToColumns Destination:=Range("A6"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="#"

End Sub

 

[Alan]

Hi Alan,

I mis-read.

No worries - I thought I was missing something at my end!

Try:

Sub Tester()

Selection.Replace What:=" ", _
Replacement:="#", _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
MatchCase:=False

Selection.TextToColumns Destination:=Range("A6"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="#"

End Sub

Superb - so simple and obvious (now) of course!

Thank you so much - I really appreciate your assistance.

Regards,

Alan.