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.
Many thanks
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.
osk
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.
CLEAN function
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
Hi Guys, I am trying to insert one picture is the mistake. Anyone can help me ?? Thank you, Eric My code . . . . function openAndPasteInExcel() { var excel = new ActiveXObject('Excel.Application'); var workBooks = excel.Workbooks.Add; var excelSheet = workBooks.Worksheets(1); excelSheet.Application.Visible = true; excelSheet.Activate(); excel.ActiveSheet.Range('A1').Select; excel.ActiveSheet.PasteSpecial(0, false, false); return excel; } . . . . . strPicName = "C: \ TESTE \ LEGENDE_HEBDOMADAIRE_EXCEL.png"; excel.ActiveSheet.PageSetup.LeftFooterPicture.Filename = strPicName; excel.ActiveSheet.PageSetup.LeftFooter
Hi, I am using Microsoft.Office.Interop.Excel to export the System.data Datatable values to the MS Excel. using Excel = Microsoft.Office.Interop.Excel; Excel.Application App = null; Excel._Workbook WB = null; Excel._Worksheet WS = null; / / Start Excel and get Application object. App = new Excel.Application(); App.Visible
HI, This is the error I am getting when trying to open the exported file in excel: Excel cannot open the file "Report.xlsx" because the file format or extension is not valid Color.Black Response.Clear() Response.Buffer = True Response.ContentType = " application / vnd.openxmlformats-officedocument.spreadsheetml.sheet"- -- excel 2007 format Response.Charset = "" Page.EnableViewState = False Dim MyFileName As String = "" MyFileName = "Sales" & ".xlsx"- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -excel 2007 extension Response.AddHeader( "Content-Disposition" , "attachment;filename = " & MyFileName) Dim tw As New System.IO only when you send a workbook for review. The workbook file is saved as an Excel binary workbook. However, the workbook has an .xlsx extension that is appended to the file workbook as one of the following file formats before you send the workbook for review: * Excel Binary Workbook (*.xlsb) * Excel 97-2003 Workbook (*.xls) Delete your code and put below one
private void UpdateDigitalsheet() { try { / * Excel Object declaration * / / / Excel.Application oXL = null; / / Excel._Workbook oWB = null; Excel._Worksheet oSheet = null; Excel._Worksheet oSheet1 = null; / / Declare the Control Naming convention string CtrlLevelDropDown = string.Empty; string CtrlRankDropDown = string user. if (U_Name = = "") { Session["CreatedFileName"] = DesgFileName; if (CopiedMasterExcel(DesgFileName)) { / / Create web Appliction Object for Access Excel oXL = new Excel.Application(); Session["CompanyName"] = txtCustomerName.Text; Session["ExcelMainObj"] = oXL; / / Open the Excel oWB = oXL.Workbooks.Open
I want to fill data in to text boxes from excel sheet. And I want to write results and data to excel sheet. How to write this? HI read an excel using System; using System.IO; using System.Reflection; using NUnit.Framework; using ExcelTools = Ms.Office; using Excel = Microsoft.Office.Interop.Excel; namespace Tests { [TestFixture] public class ExcelSingle { [Test] public void ProcessWorkbook() { string file = @"C: \ Users \ Chris \ Desktop \ TestSheet.xls"; Console.WriteLine(file); Excel.Application excel = null; Excel.Workbook wkb = null; try { excel = new Excel.Application(); wkb = ExcelTools.OfficeUtil
end of post end of post keywords: Microsoft Excel Excel macro VBA, Microsoft Excel, macro, VBA description: Excel macro VBA question end of post 06-Jun-13 10:23 AM
end of post Your post shows "end of post" message. Can you post your query again. keywords: Microsoft Excel, Microsoft Excel excel description: excel and word merged end of post 06-Jun-13 10:17 AM