| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
What is this Strange Characet? Find/Replace
Hey guys-
Got a CSV file here that has an address field using a weird character to separate the address line from the city/state/zip. The character is a square box. I'm trying to do a find/replace on the thing, but, I don't know what it is to be able to tell excel to find it. I've tried to copy/paste it into the Find/Replace search box, but it won't recognize it. Can someone tell me what this character is (is it a 'return' signal, a tab, what?) so that I can replace it with a normal character in excel? Thanks! D |
|
#2
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
What is this Strange Characet? Find/Replace
Chip Pearson has an addin that can help you find out what is exactly in that
cell. http://www.cpearson.com/excel/CellView.htm If it turns out to be "nice", you can use Edit|Replace what: alt-xxxx (use the numbers on the number keypad--not above the QWERTY keys) with: (spacebar) or whatever you want. This can work nicely with alt-enters (alt-0010), but will fail with other characters (alt-0013 for example). You could use a macro to clean them up: Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim iCtr As Long myBadChars = Array(Chr(yy), Chr(zz)) For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), Replacement:=" ", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Next iCtr End Sub Change the yy/zz to what Chip shows (and you can drop ", chr(zz) if you only have one offending character). (And I changed them to space characters.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm D wrote: Hey guys- Got a CSV file here that has an address field using a weird character to separate the address line from the city/state/zip. The character is a square box. I'm trying to do a find/replace on the thing, but, I don't know what it is to be able to tell excel to find it. I've tried to copy/paste it into the Find/Replace search box, but it won't recognize it. Can someone tell me what this character is (is it a 'return' signal, a tab, what?) so that I can replace it with a normal character in excel? Thanks! D -- Dave Peterson |
|
#3
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
What is this Strange Characet? Find/Replace
Try downloading my CellView add-in, available at
http://www.cpearson.com/excel/CellView.htm . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "D" wrote in message news:svapd.75121$EZ.36941@okepread07... Hey guys- Got a CSV file here that has an address field using a weird character to separate the address line from the city/state/zip. The character is a square box. I'm trying to do a find/replace on the thing, but, I don't know what it is to be able to tell excel to find it. I've tried to copy/paste it into the Find/Replace search box, but it won't recognize it. Can someone tell me what this character is (is it a 'return' signal, a tab, what?) so that I can replace it with a normal character in excel? Thanks! D |
|
#4
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
What is this Strange Characet? Find/Replace
BTW- I did a =CHAR(A1) on it and it comes back as 11.
So how do I do a find/replace on this thing now? Thanks! D "D" wrote in message news:svapd.75121$EZ.36941@okepread07... Hey guys- Got a CSV file here that has an address field using a weird character to separate the address line from the city/state/zip. The character is a square box. I'm trying to do a find/replace on the thing, but, I don't know what it is to be able to tell excel to find it. I've tried to copy/paste it into the Find/Replace search box, but it won't recognize it. Can someone tell me what this character is (is it a 'return' signal, a tab, what?) so that I can replace it with a normal character in excel? Thanks! D |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What is this Strange Characet? Find/Replace
There are a couple of ways you could try to figure this out. The squar character shows up when the character's number doesn't have corresponding symbol (like a letter) defined in the font. Maybe yo could try a couple of other fonts in your text editor to see if i changes into something that you can then figure out. Another option i to run your file through a little vb code that checks what the cha number is at the position that this character appears in your file: 'myString has the text, and the character is at position 10 debug.print asc(mid(myString,10,1)) or something like that... -- talkswithnumbe ----------------------------------------------------------------------- talkswithnumber's Profile: http://www.excelforum.com/member.php...fo&userid=1684 View this thread: http://www.excelforum.com/showthread.php?threadid=32030 |
|
#6
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
What is this Strange Characet? Find/Replace
In the Find box, hold down the ALT key and type 0011 on the
numeric keypad (not the number keys above the letters). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "D" wrote in message news:VCapd.75122$EZ.14343@okepread07... BTW- I did a =CHAR(A1) on it and it comes back as 11. So how do I do a find/replace on this thing now? Thanks! D "D" wrote in message news:svapd.75121$EZ.36941@okepread07... Hey guys- Got a CSV file here that has an address field using a weird character to separate the address line from the city/state/zip. The character is a square box. I'm trying to do a find/replace on the thing, but, I don't know what it is to be able to tell excel to find it. I've tried to copy/paste it into the Find/Replace search box, but it won't recognize it. Can someone tell me what this character is (is it a 'return' signal, a tab, what?) so that I can replace it with a normal character in excel? Thanks! D |
|
#7
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
What is this Strange Characet? Find/Replace
ok- tried doing Alt+0011 and it said it couldnt find anything to replace. I
also tried a Clean() function, and THAT DID work- but, I dont want to delete the character since I need to find/replace and then go text to columns with it. Any other ideas? Thanks guys! D "Chip Pearson" wrote in message ... In the Find box, hold down the ALT key and type 0011 on the numeric keypad (not the number keys above the letters). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "D" wrote in message news:VCapd.75122$EZ.14343@okepread07... BTW- I did a =CHAR(A1) on it and it comes back as 11. So how do I do a find/replace on this thing now? Thanks! D "D" wrote in message news:svapd.75121$EZ.36941@okepread07... Hey guys- Got a CSV file here that has an address field using a weird character to separate the address line from the city/state/zip. The character is a square box. I'm trying to do a find/replace on the thing, but, I don't know what it is to be able to tell excel to find it. I've tried to copy/paste it into the Find/Replace search box, but it won't recognize it. Can someone tell me what this character is (is it a 'return' signal, a tab, what?) so that I can replace it with a normal character in excel? Thanks! D |
|
#8
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
What is this Strange Characet? Find/Replace
When I tried alt-0011, it didn't work.
You may end up using the macro. You could use a helper column of cells with formulas: =SUBSTITUTE(A1,CHAR(11)," ") then drag down, copy|paste special|values over the original range and delete the helper column of formulas. (I'd try the macro--it looks quicker.) D wrote: BTW- I did a =CHAR(A1) on it and it comes back as 11. So how do I do a find/replace on this thing now? Thanks! D "D" wrote in message news:svapd.75121$EZ.36941@okepread07... Hey guys- Got a CSV file here that has an address field using a weird character to separate the address line from the city/state/zip. The character is a square box. I'm trying to do a find/replace on the thing, but, I don't know what it is to be able to tell excel to find it. I've tried to copy/paste it into the Find/Replace search box, but it won't recognize it. Can someone tell me what this character is (is it a 'return' signal, a tab, what?) so that I can replace it with a normal character in excel? Thanks! D -- Dave Peterson |
|
#9
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
What is this Strange Characet? Find/Replace
Replace it with a character that isn't used. Maybe the vertical bar (|).
Then use that in your data|text to columns. (I'd still use that other macro <bg.) D wrote: ok- tried doing Alt+0011 and it said it couldnt find anything to replace. I also tried a Clean() function, and THAT DID work- but, I dont want to delete the character since I need to find/replace and then go text to columns with it. Any other ideas? Thanks guys! D "Chip Pearson" wrote in message ... In the Find box, hold down the ALT key and type 0011 on the numeric keypad (not the number keys above the letters). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "D" wrote in message news:VCapd.75122$EZ.14343@okepread07... BTW- I did a =CHAR(A1) on it and it comes back as 11. So how do I do a find/replace on this thing now? Thanks! D "D" wrote in message news:svapd.75121$EZ.36941@okepread07... Hey guys- Got a CSV file here that has an address field using a weird character to separate the address line from the city/state/zip. The character is a square box. I'm trying to do a find/replace on the thing, but, I don't know what it is to be able to tell excel to find it. I've tried to copy/paste it into the Find/Replace search box, but it won't recognize it. Can someone tell me what this character is (is it a 'return' signal, a tab, what?) so that I can replace it with a normal character in excel? Thanks! D -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What is this Strange Characet? Find/Replace
ok- pasted it into word and it just moved down a line like I did a hard
return or something. Tried all different fonts and nothing. I have no idea how to go about doing the VB route- any more hints on doing that? Man this is getting to be a pain... Thanks! D "talkswithnumber" wrote in message ... There are a couple of ways you could try to figure this out. The square character shows up when the character's number doesn't have a corresponding symbol (like a letter) defined in the font. Maybe you could try a couple of other fonts in your text editor to see if it changes into something that you can then figure out. Another option is to run your file through a little vb code that checks what the char number is at the position that this character appears in your file: 'myString has the text, and the character is at position 10 debug.print asc(mid(myString,10,1)) or something like that.... -- talkswithnumber ------------------------------------------------------------------------ talkswithnumber's Profile: http://www.excelforum.com/member.php...o&userid=16841 View this thread: http://www.excelforum.com/showthread...hreadid=320304 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What is this Strange Characet? Find/Replace
You could read David McRitchie's notes:
http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side. Paste the code there. Then hit alt-f11 to get back to excel. then hit alt-f8 (or tools|macro|macros) and select the macro and click run. Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim iCtr As Long myBadChars = Array(Chr(11)) For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), Replacement:="|", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Next iCtr End Sub I replaced it with the vertical bar (|) in the code above. D wrote: ok- pasted it into word and it just moved down a line like I did a hard return or something. Tried all different fonts and nothing. I have no idea how to go about doing the VB route- any more hints on doing that? Man this is getting to be a pain... Thanks! D "talkswithnumber" wrote in message ... There are a couple of ways you could try to figure this out. The square character shows up when the character's number doesn't have a corresponding symbol (like a letter) defined in the font. Maybe you could try a couple of other fonts in your text editor to see if it changes into something that you can then figure out. Another option is to run your file through a little vb code that checks what the char number is at the position that this character appears in your file: 'myString has the text, and the character is at position 10 debug.print asc(mid(myString,10,1)) or something like that.... -- talkswithnumber ------------------------------------------------------------------------ talkswithnumber's Profile: http://www.excelforum.com/member.php...o&userid=16841 View this thread: http://www.excelforum.com/showthread...hreadid=320304 -- Dave Peterson |
|
#12
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
What is this Strange Characet? Find/Replace
It is CODE that you want to use and you must hit the correct
position as code only works for one character. http://www.mvps.org/dmcritchie/excel...tm#debugformat Instead of using the likes of =CODE(MID(A1,14,1)) you might try Chip Pearson's function. Do you just want to replace them with spaces or were you planning to separate the cell as if using Data, Text to Columns. Didn't think you had a 0011 because that is not typical. http://www.mvps.org/dmcritchie/rexx/htm/symbols.htm -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "D" wrote in message news:VCapd.75122$EZ.14343@okepread07... BTW- I did a =CHAR(A1) on it and it comes back as 11. So how do I do a find/replace on this thing now? Thanks! D "D" wrote in message news:svapd.75121$EZ.36941@okepread07... Hey guys- Got a CSV file here that has an address field using a weird character to separate the address line from the city/state/zip. The character is a square box. I'm trying to do a find/replace on the thing, but, I don't know what it is to be able to tell excel to find it. I've tried to copy/paste it into the Find/Replace search box, but it won't recognize it. Can someone tell me what this character is (is it a 'return' signal, a tab, what?) so that I can replace it with a normal character in excel? Thanks! D |
|
#13
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
|
What is this Strange Characet? Find/Replace
Looks like there is actually a 0011 it is VT (vertical tab)
never heard of it. Though from some of your replies, it doesn't sound like that is the character unless you did not proper use ALT+0011 from the numeric keypad http://www.december.com/html/spec/ascii.html --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
| where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
| find and replace macro strange behaviour | Excel Discussion (Misc queries) | |||
| find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
| Strange results using .FIND in Excel VBA | Excel Programming | |||
![D[_6_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
