edit out ascii char 09
Hi.
I have a sheet created in SAS that has ascii char 09 which displays on the screen as a small square box (but prints OK), If I use the clear function, it removes the character all together leaving no space between the words. I can do a replace, but since this is a large sheet, and the cells have anywhere from zero to five char 9's, I have not been able to figure out a way of fixing this. Any suggestions |
You could use a helper cell with a formula:
=substitute(a1,char(9)," ") Then copy|paste special|values and toss the bad column. Or maybe you could run a little macro... If you want to replace these characters with something else (space or nothing???), you could use a macro to do the edit|replace's: Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim iCtr As Long myBadChars = Array(Chr(9)) For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), Replacement:=" ", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Next iCtr End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm rugbyba wrote: Hi. I have a sheet created in SAS that has ascii char 09 which displays on the screen as a small square box (but prints OK), If I use the clear function, it removes the character all together leaving no space between the words. I can do a replace, but since this is a large sheet, and the cells have anywhere from zero to five char 9's, I have not been able to figure out a way of fixing this. Any suggestions -- Dave Peterson |
All times are GMT +1. The time now is 09:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com