I have an excel file downloaded from a third party database.  In each cell, multiple names are listed, separated by an ASCII carriage return.  I am trying to parse the data into separate cells.  I've tried the text to columns, as well as copy and paste special transpose.  No luck.  So I called the Microsoft support and got to the group you have to pay to provide an answer.  They told me it couldn't be done.  I know this can be done, though.  I'm using Excel 7.

Does anyone have any suggestions?

Do you mean carriage returns or linefeeds?  There's a difference.

If you mean linefeeds (like alt-enter's when you do it manually), then select
your column and use:

Data|Text to columns (xl2003 menus)
Delimited
Use Other, but use ctrl-j as the delimiter character.
And finish up that wizard.

If you mean carriage control (hex 012), then you can use a macro to change that
to any unused character in your range (maybe the broken vertical bar:  |).

Saved from a previous post.

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.aspx

Depending on what that character is, you may be able to use alt-#### (from the
number keypad) to enter the character into the Other box in the text to columns
wizard dialog.

In fact, you may be able to select the character (in the formula bar), and copy
it.  Then use ctrl-v to paste into that text to columns Other box.

You may be able to use Edit|Replace to change the character--Some characters can
be entered by holding the alt-key and typing the hex number on the numeric
keypad.  For example, alt-0010 (or ctrl-j) can be used for linefeeds.  But I've
never been able to get alt-0013 to work for carriage returns.

Another alternative is to fix it via a formula:

=substitute(a1,char(##),"")

Replace ## with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

    Dim myBadChars As Variant
    Dim myGoodChars As Variant
    Dim iCtr As Long

    myBadChars = Array(Chr(##))  '<--What showed up in CellView?

    myGoodChars = Array("") 

    If UBound(myGoodChars) <> UBound(myBadChars) Then
        MsgBox "Design error!"
        Exit Sub
    End If

    For iCtr = LBound(myBadChars) To UBound(myBadChars)
        ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
            Replacement:=myGoodChars(iCtr), _
            LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False
    Next iCtr

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

JMathews wrote:

I have an excel file downloaded from a third party database.  In each cell, multiple names are listed, separated by an ASCII carriage return.  I am trying to parse the data into separate cells.  I've tried the text to columns, as well as copy and paste special transpose.  No luck.  So I called the Microsoft support and got to the group you have to pay to provide an answer.  They told me it couldn't be done.  I know this can be done, though.  I'm using Excel 7.

Does anyone have any suggestions?

--

Dave Peterson

• Marked as answer by Sally Tang Thursday, September 30, 2010 5:27 AM

Okay, using the formula bar with substitute on CHR(30) fixed my problem.

Thanks.