Strange Character

[jonzel]

There is a strange character appearing throughout my data.
It is a square. I think its a formatting character.
I want to delete this character, but I have far too much data to delete them one at a time.
I cannot copy and paste it into find/replace.
Any ideas on how to delete this character.

[shg]

Try the CLEAN() function.

Edit: Or, you can use the CODE() function to find out what the character is, and do Replace to delete it. So if the character is a line feed (10), then in the Find dialog, hold down the Alt key, and on the numeric keypad, enter 010, and in the Replace dialog, enter nothing or a space.

[jonzel]

thanks for the advice.
clean funtion does work.

but I would like to know about this other method.
so, I followed the first step and found that the numeric code for this character is 13.
but when I go to edit - find, and hold down the alt key to type, I cannot type anything.
am I missing something?

would I have to convert all the characters to numbers and just search for the # 13?, Or somehow search for this character by typing its numeric code via the alt key?

[shg]

It's a carriage return, and CLEAN does remove it; i just did it.

edit - find, and hold down the alt key to type, I cannot type anything.
am I missing something?

Yes:

hold down the Alt key, and on the numeric keypad, enter 010

Not the number keys below the symbols, the numeric keypad, and enter the three-digit code (013 for carriage return)

[jonzel]

I am using the numeric keypad.
and when I hold down Alt, I cannot type anything

[shg]

You won't see anything if it's a control character. If you want to convice yourself it's doing something, enter 097 to see a lower case a.

[jonzel]

ok
when I type Alt 097 - I get an "a"
when I type Alt 013 - I see nothing like u say, but when I click "find all" I just get a list of all my blank cells.
so its actually searching for blank cells.

[shg]

OK, put this is a code module and run it:

Please Login or Register  to view this content.

Edit: If you want to replace the carriage returns with a space (and then delete doubles spaces if necessary later), change

Please Login or Register  to view this content.