Replacing/Removing "Special" Characters in Excel

[DanAuber]

I'm bringing data into excel via a text report from another system. On some lines I have the following character:

?


I want to remove it (find-replace it to nothing) - Can anyone help me do this ? - Obviously I've tried using find/replace but can't get this character into the find box ! (I try to paste it in - but then excel can't find it to replace it !)

Dan

 

[AnotherHiggins]

Click into the cell and highlight one such character.

[Ctrl]+[H] and paste the copied character into the Find What field. Leave the Replace With field empty. Make sure that Match entire cell contents is [!]not[/!] selected.

Replace All.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[DanAuber]

Thanks for this John - but I'm afraid it doesn't work. When I try and do the paste part nothing happens...think it's due to this character being "special"...your instructions work fine with a normal character...

 

[WinblowsME]

Find

~?

and replace with nothing.

 

[AnotherHiggins]

Did you carry through and try it? It might not appear to have entered anything into the find field, but I have successfully used that process before.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[DanAuber]

Hi John - yes I did carry it through - to no avail unfortunately.

and thanks also WinblowsME - but your suggestion doesn't seem to work either

 

[AnotherHiggins]

I think I might have been remembering that wrong, because I can't make it work now, either....

But try this:

- Double Click into a cell containing the special character
- Select one single such character (place cursor before it, then Shift]+[Right Arrow Key])
- Double Click into another, empty cell (let's say A1 for this example)
- Paste the special character into that cell
- In yet another empty cell, type in [COLOR=blue white]=code(A1)[/color] *

That will return the code for whatever character you're dealing with. For our example, let's say that you get 13 (a good ole Carriage Return). Now let's go back into Find and Replace:

- In the Find What field, hold down the [Alt] key and then use the number pad at the right side of your keyboard to type in 0013.
- Leave the Replace With field empty
- Replace All

*Alternatively, if you know what position in a field the spacial character occurs, you could just do something like =code(mid(A1,5,1))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[firefytr]

If these don't work for you (I would try char 10 and 13), copy/paste your formula bar contents of one of these cells to a post here.

The question mark is difficult to find/replace because it is a wildcard for a single character. In order to find an actual question mark you must preceed it with a tilde character, hence the "~?" find/replace characters.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci