Removing Carriage Return characyers from a cell

[bleu808]

Hi All,

I have a spreadsheet that is full of info exported from a crm. One of
my cells has characters indicating a carriage return. The character
appears as two double bold bars in the row. If I have the cell open,
then the character appears as a small box.


I am unable to use the find/replace feature for this. This character
just appears as a space when I paste it in find.

I wante to delete these characters, thus deleting the carriage return.
I can do it manually on a per cell basis - but I am working on a 10,000
row spreadsheet, and that is just not gonna happen!

Thanks in advance for your help!

 

[Gord Dibben]

In the replace what: box you hold ALT key and typed 0010 or 0013 on the
numpad.

You will see nothing entered, but it is being entered.

If no go, download Chip Pearson's CELLVIEW add-in from

http://www.cpearson.com/excel/CellView.htm

Load through Tools>Add-ins.

Select the data and Chip's add-in will show you what the characters are.

The rectangles could be any one of a number of different characters.

David McRitchie's TRIMALL macro could also be of assistance.

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

Gord Dibben Excel MVP

 

[bleu808]

Hello. Thank you for the suggestions. I am able to remove something
not sure what exactly by doing the ALT+F and 0013 or 0010. Instead o
2 solid bold bars, I have 1. When I click on the cell, the small boxe
still appear.

I then export the data into ACT. I run into a problem with the cell
that were changed when doing ALT F. This data does not appear. Have
altered it that much

 

[gls858]

Hi All,

I have a spreadsheet that is full of info exported from a crm. One of
my cells has characters indicating a carriage return. The character
appears as two double bold bars in the row. If I have the cell open,
then the character appears as a small box.


I am unable to use the find/replace feature for this. This character
just appears as a space when I paste it in find.

I wante to delete these characters, thus deleting the carriage return.
I can do it manually on a per cell basis - but I am working on a 10,000
row spreadsheet, and that is just not gonna happen!

Thanks in advance for your help!

How are you exporting it? When you open the file do you get
the import wizard? If you do, then you should be able to isolate
the double bars as it's own column. Then you can just delete that
column. If that doesn't work you may be able to use the text to columns
feature to isolate the characters.

gls858

 

[BrianB]

I seem to remember that there is an Edit/Replace method, meanwhile make
a new column with formula eg.
=CLEAN(a1)
Select the column. Copy. Edit/Paste Special -Values.
Delete the original column.