Microsoft Excel - Excel 2007 how do I replace a special charcter (a square box with a question mark in it)

I have a spreadsheet which contains email addresses where all the email addresses for one family are in one cell separated by a strange looking character which is a square box with a question mark in it.  I would like to replace this character with a smicolon so I can paste into Outlook.  However, when I copy the character I can't then paste it into the replace dialog box (ie nothing happens when I press ctrl v).  Strangely it will paste the character into other empty cells.

When I changed the font to Arial the character changed into an empty square box but I still can't copy and paste it into the replace box (but I can into empty cells).

Any help/ideas would be gratefully received.

Many thanks

It sounds like you are seeing a character representation of a carriage return.

Here's the trick (adapted from the answer at this link - http://www.mrexcel.com/forum/showthread.php?t=341057 ) -

Try getting the numeric code of the strange looking character using the CODE function. For instance, the forumula =CODE("!") will display the numeric code for ! (33)

Invoke the Find box using the shortcut Ctrl + F

Click in the Find box, hold Alt key and type 0 followed by numeric code of the mysterious character you have encountered, on the number keypad then the release the Alt key (For example, for "!", you would type 033 instead of 33).  As soon as you release the Alt key the character whose numeric code you typed will appear in the Find box.

Now go to the Replace tab, place the semi-colon box in the "Replace with" textbox, and hit Replace All.

Many, many thanks for these.  I found out it is was character code 11 and so can swap it for a semi colon with the substitute formula in the link supplied http://www.mrexcel.com/forum/showthread.php?t=341057

However, I can't get the method you described to work because I am using a laptop and so have no extended keyboard.  I have tried using the fn command to get access to the pseudo numeric keypad on the right hand side of the keyboard and also tried using the on screen keyboard (osk.exe) but neither will work in the find dialog box.  Both do work in a cell!

How can I add special charcters to a find box?

Many thanks in advance

I was able to replicate the process of pasting the character using the On-Screen Keyboard on Vista & successfully replace a test character. After doing a Ctrl+F in Excel, while the cursor was in the Find box, I pressed the Alt key in the On-Screen Keyboard & to show that it is "sticky", it highlights key with a red border. I then typed the numeric code & finally clicked on the Alt key to release it's hold. Immediately, the character representing the numeric code appeared in the Find box.

Make sure you add a 0 to the numeric code of character & release the Alt key after you finish typing.

Thank you - I have now realised that I can use the OSK to add other special characters to the find box but it doesn't add anything for code 011, or code 0011.

Can you see if you can add a character with that code.

Again very many thanks for your help.

True, I was not able to add anything for 011 either.

Excel Help suggests in the topic "Remove spaces and nonprinting characters from text" that you can use a combination of the TRIM, CLEAN, and SUBSTITUTE functions to remove non printing characters - http://office.microsoft.com/en-gb/help/HP030561311033.aspx

See if the CLEAN function helps

I had the same problem.  Mine was a carriage return - Code 13.  Typing in the alt code did nothing in the replace box.

I used this: =SUBSTITUTE(A1,CHAR(13),"")