Text code removal

[Alfred]

Hi,
Does anyone have experience in removing a code (I think it
is a return or enter code from the application that
created the database)from a cell?

Specifically, I have a spread sheet which has been
downloaded to Excel but certain cells are much wider than
is necessary due to a vertical box at the end of line
within a cell. This makes the entire row inordinately wide.
I can highlight and copy the offending character but
canot 'paste' it in Find and Replace in order to eliminate
it.

TIA

 

[Gord Dibben]

Alfred

If you have spaces or other junk in cells, you can try the TRIM or CLEAN
Functions.

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.

If carriage returns you may be able to Replace them using ALT + 0010 or 0013

Most likely David McRitchie's TRIMALL macro will be of greatest assistance.
Cleans up what TRIM and CLEAN miss.

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


Gord Dibben Excel MVP

 

[Alfred]

Thanks Gord,
I loaded CellView, what a useful tool! (& Thanks Chip for
that add-in function!)
This may seem amusing but how can I enter Alt + 0013 and
0010 in the Find what: and Replace with: dialogue box. I
get beeps when I try to.
Those are my target codes and with over 4000 rows I hope I
can do a mass removal in order to make one line out of the
text levels.

TIA

 

[Gord Dibben]

Alfred

In the replace what: box just hold ALT key and enter 0010 from the
NumPad(right side of keyboard).

You won't see anything, but it is there.

Do not enter anything(or enter a <space>) in the replace with: box

Gord

 

[Alfred]

Thanks again Gord, you've made life a bit easier today.
That did the removal and consolodation in under 10 seconds!

 

[Gord Dibben]

Coooool!

Gord

Thanks again Gord, you've made life a bit easier today.
That did the removal and consolodation in under 10 seconds!