Remove carriage returns from a cell

[sanand]

Good Morning Experts,

I'm trying to convert a text file into Excel
spreadsheet. When I open the text file with
Excel, the spreadsheet shows that there are
some "Carriage Return" Keys used in some cells.

If I remove these CrLFs mannually from the
text file, it looks fine, but this is very
time consuming.

I need assistance in removing these
CrLf keys programatically.

Thanks in advance,

 

[Aladin Akyurek]

One way:

=SUBSTITUTE(A1,CHAR(10),"")

 

[Jay Petrulis]

Hi,

=CLEAN(A1)

will remove *ALL* non printable characters from the reference cell.

<pre>Sub test()
Dim UsedCell As Range

For Each UsedCell In Selection
If Not UsedCell.HasFormula Then
UsedCell = WorksheetFunction.Clean(UsedCell)
End If
Next UsedCell
End Sub</pre>

will do the same thing for all the cells in the range selected, without having to use intermediate formulas.

Change to Aladin's suggestion of Substitute to remove only the carriage returns in VBA. Note that CHAR(x) in Excel is Chr(x) in VBA.

<pre>UsedCell = WorksheetFunction.Substitute(UsedCell, Chr(10), "")</pre>

 

[sanand]

Thank you Jay and Aladin for your
immediate response.

Both these functions either don't work for
me or I don't know how to use them.

This is how I used them:
1. Highlighted the column in excel
2. Go to Insert -> function
3. Added =Substitute($ColRowNum,Char(10),"")
4. There is some sort of error or sometimes
no error at all.

When I open the text file in XLS, and
used the delete key at the end of the line
does not change the result.

Please see the attached text file that I
want to open in excel. The first col.
should have the numbers. In the 1st column
you'll see some text which is coming from
the last column of the previous row (record).
Please see the attached file.

Thanks for your assistance.
sanand

PS: I'm sorry I can't find any link to
attach a file.

 

[sanand]

Thank you Jay and Aladin for your
immediate response.

Both these functions either don't work for
me or I don't know how to use them.

This is how I used them:
1. Highlighted the column in excel
2. Go to Insert -> function
3. Added =Substitute($ColRowNum,Char(10),"")
4. There is some sort of error or sometimes
no error at all.

When I open the text file in XLS, and
used the delete key at the end of the line
does not change the result.

Please see the attached text file that I
want to open in excel. The first col.
should have the numbers. In the 1st column
you'll see some text which is coming from
the last column of the previous row (record).
Please see the attached file.

Thanks for your assistance.
sanand

PS: I'm sorry I can't find any link to
attach a file.

Would you please walk me through as to
how to use/create a function to solve
this mystery?