Remove Box or Squre Characte from Cells

After doing an export from Outlook/Contacts to Excel I notice a square or box 
like character in all my cells. 

Is there an easy way to remove it, find and replace does not work.

Thanks



0
Corey (35)
11/15/2005 5:26:19 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
457 Views

Similar Articles

[PageSpeed] 25

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

If you do see a box, then you can either fix it via a helper cell or a macro:

=substitute(a1,char(9),"")
or
=substitute(a1,char(9)," ")

or as 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(9), Chr(160))  '<--What showed up in CellView?
    
    myGoodChars = Array(" "," ")     '<--what's the new character?
    
    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, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Corey wrote:
> 
> After doing an export from Outlook/Contacts to Excel I notice a square or box
> like character in all my cells.
> 
> Is there an easy way to remove it, find and replace does not work.
> 
> Thanks

-- 

Dave Peterson
0
petersod (12005)
11/15/2005 5:36:29 PM
Reply:

Similar Artilces:

Pop Up Combo Box
I have 3 options in a combo: TS, CT, and MISC Each one of these options has their own pertinent account codes. What I want is the ability to choose one of the options (TS, CT or MISC), and based on that choice, have another combo box pop up with a list of corresponding account codes. If you can help with step-by-step moron-proof instructions, I would be much obliged. This is called "cascading combo boxes". A google search should return plenty of links that will show you how to do it. HTH -- _________ Sean Bailey "natalie" wrote: > I have 3 options in a combo...

Hyperlink via indirect cell reference
Hi I have workbook that contains a number of sheets. On a separate sheet I would like to be able to insert a hyperlink so that I can jump to a specific sheet. However, rather than inserting all of the hyperlinks manually (I will have to replicate this over many workbooks) I wondered if there was a formula to allow me to jump to a cell (say A1) in another worksheet, based on the name of that worksheet being entered in a cell reference. For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", "Sheet4". In another sh...

Autofilter to search characters that begin with "* "
I'm having difficulty getting Autofilter to work with text that begins with "* ". It appears that * is a wild card, but I don't want to search that way. Any suggestions? Thanks, Barb Reinhardt So..you're trying to use a custom filter within Autofilter? Use Begins with and then, in the text box, precede the asterisk with a tilde ~* "Barb Reinhardt" wrote: > I'm having difficulty getting Autofilter to work with text that begins with > "* ". It appears that * is a wild card, but I don't want to search that > way. Any s...

How do I freeze or lock cells to show up on each page without typ.
I have a 4 page sheet. I have a header already. But I want to freeze the cells that head up the first page. I've done it before in school but can't remember what it is called or how to do it...that's why I'm doing this. Anyway, I want these cells to print off on each new page without having to type them on each page. I hope that makes sense and I hope that someone can help me! If you mean for printing do file>page setup>sheet and select rows to repeat at top otherwise for viewing you can select a2 if the headers start in row 1 and do window> freeze panes ...

Rename Cell
How can I rename column A to read "bills" instead of the letter A? You can't. The closest you will get is to hide column headings, via Excel Option, and then create your own. -- Regards Dave Hawley www.ozgrid.com "shoe" <shoe@discussions.microsoft.com> wrote in message news:DBA970DF-D928-41EE-9565-4639E7D49BCE@microsoft.com... > How can I rename column A to read "bills" instead of the letter A? you cant change the headers or row labels but you can define you data as a list (or table) and the headings can then be used to refer...

merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell? Hi Bob Not possible I'm afraid. Try placing the dat from both cells into one and use "Center across selection" under Format>Cells>Alignment Merge cells always end up causing grief. they are best avoided. ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** "bob" <bobree@hotmail.com> wrote in message news:%23JuOM9HGEHA.2308@tk2msftngp13.phx.gbl... > How can I merge 2 cells without losing data from the other...

selected row count of list box
A2k Is there a way to get the selected row count of a list box dynamically as the user selects rows? Delphi has an event called "OnSelectionChanged" but Access is much more limited. I don't want the user to have to exit the list box or click a button or anything manual in order to see the # of rows he's selected. How can this be done? I know about "lstCusts.ItemsSelected.Count" but not sure what event to use it in to accomplish what I need. Thanks, Keith Never mind. I did this and it handles both mouse and keyboard selections: Private Sub lstCusts_AfterUpdat...

Create static text from cell reference
Hey everyone... I have two columns of text which I'm combining in a third column using the formula (for C1, for example) =A1 & char(10) & B1 This gives me the contents of A1 on a line above the contents of B1 and works fine. What I NEED to do is somehow create column C as TEXT, not as a REFERENCED data from columns A and B. How do I create a cell that contains the actual TEXT content of another cell instead of a REFERENCE to the other cell? TIA... Select all the cells in "C" that have content. R-click them and select "Copy" then r-click again, sele...

Cell Format #4
Is there a way to have a cell format based on contents of an i statement... Example if(C1="Input",and(C3,Format $#.##),if(C1="% of Revenue",and(C5,Forma #.##%),na) I want the If statement to test a condition, return contents of th correct cell and format automatically. Any help is appreciated -- bforster ----------------------------------------------------------------------- bforster1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1177 View this thread: http://www.excelforum.com/showthread.php?threadid=26133 You can't change the fo...

Getting contents of adjacent cells
I want to divide the y1-axis column and save it to radius (y1/2) column. How do I do that? x-axis y1-axis radius(y1/2) 0 0.00 8.0000 1 0.25 8.0242 2 0.50 8.0691 3 0.75 8.1281 4 1.00 8.1989 5 1.25 8.2803 6 1.50 8.3716 7 1.75 8.4729 8 2.00 8.5832 hi divide the y1-axis by what? 2 as an guess with y1-axis in column c in the y1/2 column(d?), enter =C2/2 copy down. regards FSt1 "Rocky" wrote: > I want to divide the y1-axis column and save it to radius (y1/2) column. How > do I do that? > > x-axis y1-axis radius(y1/2) > 0 ...

cell selection gone crazy on Excel 2003
All of a sudden the mouse is acting like it is held down, and will not stop selecting cells. Have tried double clicking, playing with the Function keys, all sorts of things, but to no avail... don't want to force quit. Any clues? TIA, Geri Hi Geri, See David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/ghosting.txt --- Regards, Norman "Tweedie-Vaughan" <Tweedie-Vaughan@discussions.microsoft.com> wrote in message news:438C3854-C74C-410A-BD88-DAA146172E99@microsoft.com... > All of a sudden the mouse is acting like it is held down, a...

Average of logic cells
I used a logic test to determine some levels from raw scores. For EG >120 =5, 119-110 = 4, etc. I now want to dtermine an average score of several of the the results from the logic tests but it doesnt seem to work. (AVG does not recognise cells with logic tests) Can anyone help, please? -- ckdkvk ------------------------------------------------------------------------ ckdkvk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29245 View this thread: http://www.excelforum.com/showthread.php?threadid=489704 hi, ckdkvk ! > I used a logic test to determine so...

Formatting Linked Cells
I have a project to do. I have to create an input worksheet that is the originator of other worksheets that are linked to the input worksheet. Is there a way to have the linked cells shown as a blank cell if the data (especially text data) is not enter in the input worksheet yet. MT Hi =IF(YourLink="","",YourLink) -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "MT" <MT@discussions.microsoft.com> wrote in message news:5398D6F8-1554-46BB-B009-CCE3183C80ED@microsoft.com... > I have a project to do. I have to create an input ...

Cell References..
I have a 12 month rolling report with a seperate worksheet within th workbook which refers to the column containing current month's Numbers When I "Cut" Column C (which contains the oldest Month) and "insert column C between N & O it shifts my cells left and all I need to do i input all of the current Month Data into Column N. The formulas al remain intact and everything is peachy. Until I goto the Workshee that refers to the Current Month on the 12 month rolling report. My problem is that when I shift the columns on the "Report" workshee it chages the cell...

Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format. ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable its been helpful to me so maybe it will do good for you too: how to create a simple macro within Microsoft Excel, and then how to use = that macro to calculate a single cell value. http://sysudi.blogspot.com/2009/06/use-vba-macro-inside-excel-cell.html ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable &l...

IF statement based on True/False that activates a certain list box
I've been asked to create a formula that will react to either "yes" or "no" entered into a cell which will show options in another cell based on a certain list. Example: Cells D3:D5 contain the values Monday, Tuesday, Wednesday (list named BegWeek) Cells E3:E5 contain the values Thursday, Friday, Saturday (list named EndWeek) Cell A3 accepts either "yes" or "no" entry only from a list If A3 = "Yes" then B3 will = drop down list BegWeek If A3 = "No" then B3 will = drop down list End Week I don't even know if thi...

Changing named range reference depending on a cell's content
Where to start?! I've got the following formula pulling data in from a secon spreadsheet within the same workbook: =IF($I$7="MICH",INDEX(MICH,MATCH($D7,LOB,0),MATCH($F$5,Month,0)),0) We have 8 different locations ("MICH" being one of them) that we nee to be able to access. I can write a nested IF formula that looks a cell I7 (that contains a list of all 8 locations) and, depending o I7's content, brings back the desired values. I was hoping someone in the forum could help me write a simpler formul that would not have 7 IF statements embedded in it. Any help w...

remove shop in HQ
Hi, We have 4 shops, and we would like to delete one shop data because the data at HQ of that shop is incorrect and mixedup. However the database at shop is correct. How can we delete shop at HQ and resynchronize data from shop to HQ? We would like to clean data at HQ and upload all data include transaction from shop to HQ. Any idea? -- Jeed ...

Attachment problems
Hi, Is there anyway to unlock outlook 2002's block on ".exe" files. I know these are Level1 blocks, but can't find a way to configure it. I have recieved an important program and need to run it. Thanks in advance Scott Try here: Opening .exe Attachments with the Outlook E-mail Security Update and Outlook 2002 http://www.slipstick.com/outlook/esecup/getexe.htm -- Nikki Peterson [MVP - Outlook] "Scott White" <ddltrade@hotmail.com> wrote in message news:OmVN4JVnDHA.1672@TK2MSFTNGP09.phx.gbl... Hi, Is there anyway to unlock outlook 2002's block on...

MS Office Excel97: Removing Page Breaks
How do I remove page breaks? For some unknown reason they started appearing on a spreadsheet recently. I see dotted lines dividing up the sheet and large "Page ...." in the background. According to an Office 97 book I have, I should go to Tools->Options->View-tab and set the Page Break option off. There isn't any. If I right click outside the page area, I see PG menu items but none seem to work. There's also a PB View in the View menu, but clicking on it doesn't seem to do anything. Weird. -- Wayne T. Watson (Watson Adventures, Prop., Nevada Cit...

how can I draw a box and whisker plot in excel 2002?
I have some data, and have worked out the minimum value, the 1st and 3rd quartiles, the median and the maximum, but need to find out how to put this together in a box and whisker plot See http://peltiertech.com/Excel/Charts/BoxWhiskerH.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "andywilson" <andywilson@discussions.microsoft.com> wrote in message news:269E939E-6FC6-4993-BD6E-54ACE14141EB@microsoft.com... >I have some data, and have worked out the minimum value, the 1st and 3rd > quartiles, the median and the maximum, but ...

Need to show file properties dialog box
Hello I need to show the shell's file properties dialog box. I tried converting old vb6 code into vb.net but it gives me error. Here's my code: Private Structure SHELLEXECUTEINFO Dim cbSize As Long Dim fMask As Long Dim hwnd As Long Dim lpVerb As String Dim lpFile As String Dim lpParameters As String Dim lpDirectory As String Dim nShow As Long Dim hInstApp As Long Dim lpIDList As Long Dim lpClass As String Dim hkeyClass As Long Dim dwHotKey As Long D...

Color change in cell when > 49.99
I need a cell to change color if the value inside the cell reaches 50 or higher.either text or cell shade. just so it catches the users attention. im running excel 2000. and i have this currently in the cell that i want to aplly this to: =HLOOKUP(D20,'Hidden Data'!GZ10:HB11,2,0)*MAX(15,E20) have you tried conditional formating? format>conditional formating >-----Original Message----- >I need a cell to change color if the value inside the cell reaches 50 or >higher.either text or cell shade. just so it catches the users attention. >im running excel 2000. and i ...

All attachments are removed because deemed unsafe
Hello, Is there anything I can do in order to be able to receive attachments that are sent to me? I usually get photo's or word docs. Any suggestions will be greatly appreciated. U Which version of Outlook and what is the OS? They are probably being blocked by your Internet Security, Anti-Virus or Firewall setup. Try accessing 'help' for whatever program you have and see if there's anything for 'attachments, email attachments' or just look around for a setting to change. "U" <anonymous@discussions.microsoft.com> wrote in message news:1769401c4...

Text Box & Border Art
New Publisher 2003 user and getting quickly frustrated. I have setup a custom page size of 6.5" x 4.5" under Page Setup/Print Settings. I then created a text box of the same size, and filled it with the text I wanted. I then want to add a border to this text box. So, I right click on the text box, and choose Border Art under the Colors and Lines tab, and select the border "Twisted Lines" and hit apply. All is good. Now comes the trouble. I hit print preview and about 1/8" around the entire page is cutoff. All the margins are set to 0". I'm at a ...