Remove carriage returns from a cell

sanand

New Member
Joined
Sep 25, 2002
Messages
3
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,
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
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

New Member
Joined
Sep 25, 2002
Messages
3
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

New Member
Joined
Sep 25, 2002
Messages
3
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?
 

Forum statistics

Threads
1,144,221
Messages
5,723,102
Members
422,477
Latest member
pete101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top