If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Square Character in Excel (apostrophe)
In coverting and copying data from the Internet into excel, we constantly have square characters appear in a number of places, but in particular in place of apostrophes. We tried copying and pastin ghe square into replace, but that does not work.
Is there a way to replace the squares, or do we simply need to change the character set somehow? |
#2
|
|||
|
|||
Square Character in Excel (apostrophe)
Chip Pearson has a very nice addin that can help identify those funny
characters. http://www.cpearson.com/excel/CellView.htm If you have to do this lots of times, I'd record a macro when you do an Edit|Replace and modify it to do those funny characters: Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim myGoodChars As Variant Dim iCtr As Long myBadChars = Array(Chr(255), Chr(123)) myGoodChars = Array("'", "a") If UBound(myGoodChars) = UBound(myBadChars) Then MsgBox "Design error!" Exit Sub End If For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _ Replacement:=myGoodChars(iCtr), _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False Next iCtr End Sub Just keep adding those Hex codes you found using Chip's addin. Robmo wrote: In coverting and copying data from the Internet into excel, we constantly have square characters appear in a number of places, but in particular in place of apostrophes. We tried copying and pastin ghe square into replace, but that does not work. Is there a way to replace the squares, or do we simply need to change the character set somehow? -- Dave Peterson |
#3
|
|||
|
|||
Square Character in Excel (apostrophe)
Dave...thanks for the tool to identify the characters, but is there a way to replace them automatically using find and replace. I can find them, but I do not want to go line by line to replace them.
"Dave Peterson" wrote: Chip Pearson has a very nice addin that can help identify those funny characters. http://www.cpearson.com/excel/CellView.htm If you have to do this lots of times, I'd record a macro when you do an Edit|Replace and modify it to do those funny characters: Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim myGoodChars As Variant Dim iCtr As Long myBadChars = Array(Chr(255), Chr(123)) myGoodChars = Array("'", "a") If UBound(myGoodChars) = UBound(myBadChars) Then MsgBox "Design error!" Exit Sub End If For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _ Replacement:=myGoodChars(iCtr), _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False Next iCtr End Sub Just keep adding those Hex codes you found using Chip's addin. Robmo wrote: In coverting and copying data from the Internet into excel, we constantly have square characters appear in a number of places, but in particular in place of apostrophes. We tried copying and pastin ghe square into replace, but that does not work. Is there a way to replace the squares, or do we simply need to change the character set somehow? -- Dave Peterson |
#4
|
|||
|
|||
Square Character in Excel (apostrophe)
It depends on the character you've found.
I can change the chr(10) (vblf) manually via: Edit|Replace what: Hit and hold the alt-key and type 0010 from the number keypad--not above the QWERTY keys replace with: (whatever you want) But I can't do the same with chr(13) (vbCr). But once you find those characters (and once you know what to replace them with), you can just keep adding them to the list in the myBadChars and myGoodChars array. Then run the macro. Then save that macro for future use (don't recreate it each time). Robmo wrote: Dave...thanks for the tool to identify the characters, but is there a way to replace them automatically using find and replace. I can find them, but I do not want to go line by line to replace them. "Dave Peterson" wrote: Chip Pearson has a very nice addin that can help identify those funny characters. http://www.cpearson.com/excel/CellView.htm If you have to do this lots of times, I'd record a macro when you do an Edit|Replace and modify it to do those funny characters: Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim myGoodChars As Variant Dim iCtr As Long myBadChars = Array(Chr(255), Chr(123)) myGoodChars = Array("'", "a") If UBound(myGoodChars) = UBound(myBadChars) Then MsgBox "Design error!" Exit Sub End If For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _ Replacement:=myGoodChars(iCtr), _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False Next iCtr End Sub Just keep adding those Hex codes you found using Chip's addin. Robmo wrote: In coverting and copying data from the Internet into excel, we constantly have square characters appear in a number of places, but in particular in place of apostrophes. We tried copying and pastin ghe square into replace, but that does not work. Is there a way to replace the squares, or do we simply need to change the character set somehow? -- Dave Peterson -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro help please: Excel data to word doc | anna-maria | General Discussion | 5 | June 30th, 2004 11:53 PM |
Field code to pick up Excel sheet | Lyndie | Mailmerge | 1 | June 2nd, 2004 11:57 AM |
Recent Microsoft Excel Updates - March 24, 2004 | Rita Nikas [MSFT] | Setting up and Configuration | 0 | March 24th, 2004 05:14 PM |
Recent Microsoft Excel Updates - March 24, 2004 | Rita Nikas [MSFT] | Charts and Charting | 0 | March 24th, 2004 05:13 PM |