Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
remove square at end of data
I was opening a text file with the excel app. It is a list of serial
numbers. When I open it, there is a small square figure at the end of some of the serial numbers. What is it and how do I remove it without going to each of 20,000 lines? -- Jeff W |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
remove square at end of data
Saved from a previous post.
Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.aspx Depending on what that character is, you may be able to use alt-#### (from the number keypad) to enter the character into the Other box in the text to columns wizard dialog. In fact, you may be able to select the character (in the formula bar), and copy it. Then use ctrl-v to paste into that text to columns Other box. You may be able to use Edit|Replace to change the character--Some characters can be entered by holding the alt-key and typing the hex number on the numeric keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've never been able to get alt-0013 to work for carriage returns. Another alternative is to fix it via a formula: =substitute(a1,char(##),"") Replace ## with the ASCII value you see in Chip's addin. Or you could use a macro (after using Chip's CellView addin): Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim myGoodChars As Variant Dim iCtr As Long myBadChars = Array(Chr(##)) '<--What showed up in CellView? myGoodChars = Array("") 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 If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Jeff W wrote: I was opening a text file with the excel app. It is a list of serial numbers. When I open it, there is a small square figure at the end of some of the serial numbers. What is it and how do I remove it without going to each of 20,000 lines? -- Jeff W -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
remove square at end of data
I think you should be able to do a pretty simple find and replace. Copy the
symbol, go to the "replace" (ctrl+h in windows), paste that symbol in the "Find What" box, and leave the "Replace With" box empty. Hit "Replace All" and it should replace all intances of that symbol with nothing. Jeff W wrote: I was opening a text file with the excel app. It is a list of serial numbers. When I open it, there is a small square figure at the end of some of the serial numbers. What is it and how do I remove it without going to each of 20,000 lines? -- Jeff W |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
remove square at end of data
Try it with a few different "box" characters and report back your results.
serendipity258 wrote: I think you should be able to do a pretty simple find and replace. Copy the symbol, go to the "replace" (ctrl+h in windows), paste that symbol in the "Find What" box, and leave the "Replace With" box empty. Hit "Replace All" and it should replace all intances of that symbol with nothing. Jeff W wrote: I was opening a text file with the excel app. It is a list of serial numbers. When I open it, there is a small square figure at the end of some of the serial numbers. What is it and how do I remove it without going to each of 20,000 lines? -- Jeff W -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I a rotate my square radar chart (square-diamond)? | Charts and Charting in Excel | |||
Convert a square of data into a PivotTable? | Excel Discussion (Misc queries) | |||
how to center a square plot area in a square chart | Charts and Charting in Excel | |||
Regression Output -- R Square versus Adjusted R Square | Excel Discussion (Misc queries) | |||
plot 10 data pts in least square fit to find slope and intercept | Excel Worksheet Functions |