Microsoft Excel - Excel 2007 how do I replace a special charcter (a square box with a question mark in it)
Asked By David Cooper on 31-Aug-09 09:12 AM
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.
Matthew Johnson replied to David Cooper on 31-Aug-09 09:41 AM
It sounds like you are seeing a character representation of a carriage return.
`Excel: Find & replace a special character
mv ark replied to David Cooper on 31-Aug-09 10:39 AM
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.
special characters in find dialog box on a laptop
David Cooper replied to mv ark on 31-Aug-09 11:39 AM
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
use On-Screen Keyboard
mv ark replied to David Cooper on 31-Aug-09 12:01 PM
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.
David Cooper replied to mv ark on 31-Aug-09 12:20 PM
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.
mv ark replied to David Cooper on 31-Aug-09 12:40 PM
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
Mr Fixit replied to mv ark on 29-Nov-11 05:08 PM
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),"")
Why do some users see a special charcter (a square box with a question mark) while other don't? We are all looking it because some users have both Office 2003 & 2007 installed? Where do you see this special character? Without knowing the answer I would venture the guess this simply is related to You might want to consider to turn directly to Microsoft. Good luck, Rolf keywords: Microsoft Excel, Office 2003, Excel 2007, workbook, question mark description: Excel 2007 a special charcter (a square
formula to count charcter in a range Excel
needing a formulas that will count the charcter in a rnage please advise Excel Worksheet Discussions Microsoft Excel (1) Excel (1) SUBSTITUTE (1) SUMPRODUCT (1) COUNTIF (1) UPPER (1) LOWER (1) COUNT (1) LEN - - Don Guillett Microsoft MVP Excel SalesAid Software email@example.com Hi, For a single cell try = LEN(A1) For of "w"s or "W"s that could appear in a single cell. - - Rick (MVP - Excel) I'm guessing the answer will depend on the size of N. For example, when
how to build a formula from a string of text Excel
I want to build a dynamic formula from values from different fields and be ale a cell as Any guss on how I can do that without using Copy + Past Special / Value and then replace = sign . . . . function? Excel Worksheet Discussions Worksheet (1) INDIRECT (1) VBA (1) LEN (1) CalledEval (1) Linton (1) Alan INDIRECT(A3) But when you use INDIRECT, I don't think it likes the first charcter of the formula to be an equals sign. Look up INDIRECT() in XL's help a look at this posting from Alan Linton: http: / / groups.google.com / group / microsoft.public.excel.worksheet.functions / br = owse_frm / thread / 96bf7c7b7bdaa7d5 / 0020e3b025605e68?lnk = 3Dst&q = 3Dalan+linton+e = val# Hope
Discussions Excel (1) ExcelSalesAid (1) GuillettMicrosoft (1) SalesAid (1) Dguillett1 (1) I will certainly try to - - Don Guillett Microsoft MVP Excel SalesAid Software firstname.lastname@example.org keywords: excel description: excel
How do I Fill down on Excel Excel
Excel Miscellaneous Discussions Excel (1) RegardsDave (1) Hawley (1) http: / / www.ozgrid.com / Excel / excel-fill-handle.htm - - Regards Dave Hawley www.ozgrid.com keywords: How, do, I, Fill, down, on, Excel
Excel Discussions Excel (1) You are in the correct news group. Now try a question. Gord Dibben MS Excel MVP keywords: excel
Why doesn't the snap feature work in Excel? Excel
Excel Miscellaneous Discussions Microsoft Excel (1) Excel (1) Word (1) Bliengme (1) Snaps (1) SDI (1) Are you referring to Win7's snap feature? My Excel snaps as expected best wishes - - Bernard Liengme Microsoft Excel MVP http: / / people.stfx.ca / bliengme Taken from a post at Microsoft Answers: The snap feature that you are looking for will not work unless you open two instances of Excel. This is because Excel Unlike Word is not a True SDI Application. Microsoft is aware
how do I turn off automatic truncation of long numbers in excel? Excel
Excel Setup Discussions Microsoft Excel (1) Excel (1) Excel has a 15 significant digits limitation on numbers. Precede the long number with an apostrophe This makes the number text and cannot be used for calculations. Gord Dibben MS Excel MVP keywords: how, do, I, turn, off, automatic, truncation, of, long, numbers, in, excel?
REQ: Can I scan into Excel directly FROM Excel? Excel
Excel Worksheet Discussions Excel (1) I think this is a question for experts on your scanning software, not Excel. keywords: REQ:, Can, I, scan, into, Excel, directly, FROM, Excel?