Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Special Characters
Hello,
I exported to Excel 2003 my Outlook Calendar and now I have a lot of odd little square characters that I cannot remove. Any suggestions please? Thank you! Cathy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Special Characters
"Cathy Landry" wrote:
I exported to Excel 2003 my Outlook Calendar and now I have a lot of odd little square characters that I cannot remove. Any suggestions please? Refer to http://office.microsoft.com/en-us/ex...561311033.aspx. Basically, you can use CLEAN to remove nonprinting characters with ASCII codes 0-31. But I suspect the nonprinting characters you are seeing (hmm, an oxymoron? ;-) in the ASCII code range 127-255. You would need to use several SUBSTITUTE calls to remove each one. It would be better to use a macro. I believe someone has one; but I didn't find any with a quick Google search. Or it might be sufficient to replace CODE(160) with a space (" "). However, bewa some of those values might be printable characters in some character set. You might want to save a backup copy of the Excel file before making any changes. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Special Characters
Hi Joe,
Thank you for the response! I found a public function to use that's working great! Public Function fStripToLtrNum(strIn) 'Strips out all non-number or non-letter characters Dim strOut As String Dim iPos As Long If Len(strIn & "") 0 Then For iPos = 1 To Len(strIn) If Mid(strIn, iPos, 1) Like "[0-9A-z]" Then strOut = strOut & Mid(strIn, iPos, 1) End If Next iPos End If If Len(strOut) = 0 Then fStripToLtrNum = Null Else fStripToLtrNum = strOut End If End Function "Joe User" wrote: "Cathy Landry" wrote: I exported to Excel 2003 my Outlook Calendar and now I have a lot of odd little square characters that I cannot remove. Any suggestions please? Refer to http://office.microsoft.com/en-us/ex...561311033.aspx. Basically, you can use CLEAN to remove nonprinting characters with ASCII codes 0-31. But I suspect the nonprinting characters you are seeing (hmm, an oxymoron? ;-) in the ASCII code range 127-255. You would need to use several SUBSTITUTE calls to remove each one. It would be better to use a macro. I believe someone has one; but I didn't find any with a quick Google search. Or it might be sufficient to replace CODE(160) with a space (" "). However, bewa some of those values might be printable characters in some character set. You might want to save a backup copy of the Excel file before making any changes. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Special characters in Excel | Excel Discussion (Misc queries) | |||
Special Characters | Excel Worksheet Functions | |||
Special Characters | Excel Discussion (Misc queries) | |||
special characters (little box) | Excel Discussion (Misc queries) | |||
special characters | Excel Worksheet Functions |