Parsing on an unprintable character

[Bob Cameron]

Could anyone please advise on how I can perform a "text to
columns" or "parse" using an unprintable character as the
delimiter - in this case a paragraph mark. The cell data
split has got to happen at the point where data in the
originating cell "wraps" (i.e. goes onto a new line within
the cell). Thanks in advance. Bob Cameron

 

[kkknie]

I've never found a way to do it manually, but it is possible in code:

Just put this line in a subroutine, change it to the ranges you wan
and execute it.

Sheets("Sheet1").Range("A1:A10").TextToColumns _
Destination:=Sheets("Sheet1").Range("A1:A10"), DataType:=xlDelimited
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False
_
Semicolon:=False, Comma:=False, Space:=False, Other:=True
OtherChar:=Chr(13)

Where Chr(13) is the Carriage Return. If the paragraph resolves a
something other than Chr(13), you will need to use that as you
OtherChar.

 

[David McRitchie]

Hi Bob,
You will have to determine what the code of that
character actually is. Then you can use that character
in your Data, Text to Columns separation.

The digits must be entered from the number pad.
Alt+0010 for a line-feed
Alt+0013 for a carriage-return
Alt+0182 for a paragraph symbol ¶

http://www.mvps.org/dmcritchie/excel/join.htm#debugformat

You could use Chip Pearson's
Cell View, http://www.cpearson.com/excel/CellView.htm

But if these are where a cell wraps based on current
width of the column that would be a lot more difficult.