VBA COde To Parse Text to Column trouble

[atf32]

Have code that parses the text in a cell to columns (see below) It works well when the text is in one continual string.
example:
"July","September","October","November"

But when the data has a crlf separating each item, it does not work right. In such case, I only get the first result of "July".
example:
"July"
"September"
"October"
"November"

My Parse code:

Selection.TextToColumns Destination:=Range("E11"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="""", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5 _
, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array( _
12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array( _
25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1)) _
, TrailingMinusNumbers:=True

What can I do to handle both situations?

 

[Excel Planet]

Could you please show us a sample file so we can suggest....

 

[Fluff]

try

Selection.TextToColumns Destination:=Range("E11"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=[COLOR=#ff0000]True[/COLOR], Space:=False, Other:=True, OtherChar _
:=[COLOR=#ff0000]"" & Chr(10) & ""[/COLOR], FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5 _
, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array( _
12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array( _
25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1)) _
, TrailingMinusNumbers:=True

 

[atf32]

-- image restored --

 

[Fluff]

@atf32
This site does not allow you to upload images direct to the thread.
Either use one of the add-ins available here https://www.mrexcel.com/forum/about-board/508133-attachments.html
or upload the image to a file share site & then post the link here, but a lot of members either cannot, or will not click on external links for security reasons.

 

[atf32]

SAMPLE_File_to_Parse (1).xlsx

 

[atf32]

Appreciate the response, but this only works for cases where the string includes the carriage returns. Apparently, I don't know when I will see cells that will include them or not.

 

[Rick Rothstein]

Appreciate the response, but this only works for cases where the string includes the carriage returns. Apparently, I don't know when I will see cells that will include them or not.

The code Fluff posted in Message #3 works correctly for me. Can you post an example cell content for which it did not work for you?

 

[Fluff]

In you op you showed values that were separated by commas, but in the test file, the first sample does not have commas.
Which is correct?

 

[atf32]

Here is a link to a sample file: https://drive.google.com/open?id=1w1...toojXQdK9RfvFF