Delete cells with content that don't contain the =?UTF-8?B?wqMgc3ltYm9s?=

I have a very large messy excel file that contains some data I want to isolate. The other data is unneeded and basically in the way.

I want to delete/clear all the other cells that do not contain the ? pound symbol. This way I will be left with just the pricing info I need. 

All help appreciated


0
3/23/2012 12:46:11 PM
excel 39879 articles. 2 followers. Follow

1 Replies
1577 Views

Similar Articles

[PageSpeed] 54

On Fri, 23 Mar 2012 12:46:11 GMT, Gary N <gary.neill@allstate.com> wrote:

>I want to delete/clear all the other cells that do not contain the ? pound symbol. 

"Be careful what you wish for"

This can be done with a VBA Macro:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.
It will clear every cell that does not contain the � sign in either the text string or the numberformat string.

The caveat is that, as you requested, ALL cells that do not contain the � will be cleared.  You will find that any cells that have labels, or descriptive data, but do not contain the � sign, will also be cleared.  
Is this what you want?
If not, and if the cells you wish to process are, for example, numbers not formatted with the � sign, please be more specific about your data.  If some of that data you wish to retain is text containing the � sign, the algorithm will be different than if the data to be retained is numeric data formatted with the � sign.

===================================================
Option Explicit
Sub ClearPoundData()
    Dim rg As Range, c As Range
    Const sPound As String = "�"
Set rg = ActiveSheet.UsedRange
For Each c In rg
    If InStr(c.Text, sPound) = 0 And _
        InStr(c.NumberFormat, sPound) = 0 Then
        c.Clear
    End If
Next c
End Sub
===========================
0
ron6368 (329)
3/23/2012 1:38:43 PM
Reply:

Similar Artilces:

Replace a comma with a period in a cell containing a lastname, first name, middle i
Hello - I am trying to clean some data and need to change all of my names from McLaughlin, Victor, (i.e, comma) W to McLaughlin, Victor.(i.e., period) W Is there an extract and replace formula or method of som sort (in excel or access) that will allow me to pull the first comma from the right and replace it with a period. Thanks for any suggestions! Select the cells you want to change and run this tiny macro: Sub comma_tose() For Each r In Selection v = StrReverse(r.Value) r.Value = StrReverse(Replace(v, ",", ".", 1, 1)) Next End Sub For example: a,b,c,d wi...

Outlook delegate-mailbox deletion problem
This sounds like an easy one however, haven't found resolution yet. Here's scenario... User B reported to User A and added A to his delegation for being copied on all meeting request replies (done from Outlook 2000). All was well until User A left the company and mailbox was subsequently archived and deleted. Ever since the mailbox deletion, all attendees accepting a meeting request from User B receive an NDR referencing inability to deliver reply to User A's mailbox. The typical answer would be to open User B's Outlook app, goto delegates and remove User A.. The problem is...

Problems with re-setting the last active cell in an Excel workshee
I am trying to re-set the last active cell on an Excel 2002 worksheet (in this particular sheet it should be cell DA197). I have used both the methods described in the Knowledge Base article (deleting rows and columns and re-saving; and the Excess Format Cleaner add-in). Deleting the rows and columns does not work; using the Excess Format Cleaner does not work either and it then also hides the rows from 198 to 65536 - but does not do the same for the columns. I have checked that there is no protection on the worksheet. Has anyone else come across this problem and if so can you please ...

tasks to cell phone
How can I use exchange to send tasks to cell phone. I want to do when f.e. this phone is away from the office. This is PDA phone. On 14 Dec 2005 11:38:11 -0800, "Filip - beginner" <fwitkowski@gmail.com> wrote: >How can I use exchange to send tasks to cell phone. I want to do when >f.e. >this phone is away from the office. This is PDA phone. If you're using a Smartphone you can sync tasks already with ActiveSync. "Mark Arnold [MVP]" <mark@mvps.org> wrote: >On 14 Dec 2005 11:38:11 -0800, "Filip - beginner" ><fwitkowski@gmail...

Replace Cells with Column names in functions?
I have a # of fairly long/complex cell functions that get hard to debug because there are also a lot of rows. Is there anyway to change display so it replaces the column name e.g. If(BT1204="X". BA="Y" to If(CustomerName="X", CustomerCode="Y") ? CustomerName is a defined name range for BT1204 Find & Replace Find what: BT1204 Replace with: CustomerName "msnyc07" wrote: > I have a # of fairly long/complex cell functions that get hard to debug > because there are also a lot of rows. > > Is t...

Kamasutra Full B-Grade Movie With Nudity Must See (1 Hour 50 Min ) #2
Kamasutra Full B-Grade Movie With Nudity Must See (1 Hour 50 Min ) At http://iceboob.Co.CC Due to GOOGLE security risks, i have hidden the videos(15) in an image. in that website on Right side below search box click on image and watch videos in all angles. ...

Calendar in Cell Validation
I want to implement a cell validation such that when the user attempts to input a date, a "list" box-like functionality pops up that has a calendar and the user may then choose the date by picking with the mouse How would I implement this? Thanks Jerry Try the following Web site. http://www.fontstuff.com/vba/vbatut07.htm This site's author covers this in a tutorial, but also provides downloads. Mark <jerry.ranch@pioneer.com> wrote in message news:2r9t51pjmumjk7rjpopo7fuamg81gqkljq@4ax.com... >I want to implement a cell validation such that when the user attempts &g...

pcnetsecurity@gmail.com =?UTF-8?B?QXNzaXN0w6puY2lhIFTDqWM=?= =?UTF-8?B?bmljYSAgbWFudXRlbsOnw6M=?= =?UTF-8?B?byBkZSBjb21wdXRhZG9y?= =?UTF-8?B?ZXMgaW5mb3JtYXRpY2Eg?= =?UTF-8?B?Vml0w7NyaWEtZXMgNjM2NTI=?=
Contato: pcnetsecurity@gmail.com Contato: pcnetsecurity @ gmail.com Planos a partir de R$ 250,00 . Assist�ncia T�cnica Prestamos assist�ncia t�cnica nos computadores de sua empresa ou resid�ncia, e tamb�m possu�mos uma equipe qualificada para fazer a manuten��o no pr�prio local. - Contratos de Suporte e Manuten��o Reduza os custos de sua empresa com solicita��es de visitas t�cnicas para seus computadores, elaboramos um contrato de manuten��o integrado para sua empresa onde disponibilizamos: t�cnicos, equipamentos de suporte e substitui��o, e atendimento no hor�rio comercial ou ...

if statement #8
I am having trouble with a function. I am importing data from Filemaker that contains one column with a date for each day of the year (column B). A second column, which may or may not contain data will be a number value "MBW" (Column C". In column A, (hidden) I have a formula that returns the month of the date column "month_num". On another sheet I have a monthly table. What I want is an average of the data in "MBW" for each month. =IF(month_num=1,AVERAGE(MBW),"") should give me the January average for "MBW", but instead returns the ave...

extract info from cell, then count
I have a 2-part question: (i) I have 1000's of e-mail addresses but want to extract the countr from the e-mail i.e. abc@def.de, where de (Germany) is needed. How d I isolate the ".de" (and others eg .fr, .edu, .com etc etc) (ii) Having done the above, I then need to do a count. Rather than us COUNTIF and include the code for every country in the world, is ther any other way of counting? I guess a Pivot table? thanks, cathal..... -- Message posted from http://www.ExcelForum.com One way: =MID(A1,FIND("^",SUBSTITUTE(A1,".","^",LEN(A1)-LE...

Can I use VBA to add cells (over blanks) then do multiplication
I have a Word table in which the last column contains numbers (3 and 4) and some bank cells and I want it add them and put the total into the second last row (7 in this case). The last row contains a multiplier (3) which when applied to the total results in 21. Below is the table. | | | 3 | | | | | | | | 4 | | | | 7 | | | 3 |21| How can I achieve this in VBA (under Word 2003 and 2007) remembering that the user can add rows to the table and the last column can contain blank cells. Thanks in advance for any assistance, Peter Evans Sub ScratchMaco(...

publisher 97 #8
I have installed MS Publisher 97 on my machine running Windows ME but every time I try to run the program I get the following error message - "Can't load Custom Control DLL.'C:\WINDOWS\SYSTEM\MSPUBG40.VBX" and the program locks up. I have tried repairing the program in Add and Remove programs in control panel and have unistalled it and reinstalled it - all without success. Is there a way to overcome this problem? Rename the MSPUBG40.VBX to MSPUBG40.old and drag a new instance from your CD. Be sure the file is in the c:\windows\system folder. If the VBX file is damage...

Conditional Formatting
Is it possible to format a portion of a text string within a cell (as opposed to the entire cell). For example, I would like to format the word 'gift' in red font anywhere it a appears in range C2:C417 but only that word, not the entire cell. Not with conditional formatting. But you could change the actual format for that word (or group of characters)... Saved from a previous post (or two!): If you want to change the color of just the characters, you need VBA in all versions. You want a macro???? Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating ...

Deleted Accounts in AD
Hello! I am trying to reduce store database sizes by emptying mailboxes of accounts deleted in Active Directory. I tried creating a profile in Outlook but cannot resolve the account name. Is there a way to do this without recreating the account and reconnecting to the mailbox? I would reduce the store sizes by approx. 2 GB. Thank you. -- JoeCL LACO-CAO if you have no need for the data in the mailboxes, just purge them... -- Susan Conkey [MVP] "JoeCL" <JoeCL@discussions.microsoft.com> wrote in message news:1BA4EE14-82E1-42E7-96F3-F79BA50B5793@microsoft.com... >...

VBA to add and remove text within cells
Hi, I have a field named "Postal" at the top of column F that always include a number with 5 digits then a city name then a region name, such as "11090 CARCASSONNE Linguadoca-Rossiglione". I need to create a program to have this field changed as following : "F-11090", then copying "CARCASSONNE" into the City field which is empty (column G). The city name is always starting just one space character after the postcode, same thing for the region name, it always starts one space character after the city name. The region has to be removed completely. ...

Air Framing Nailer Kit 8-10 Gauge - 2"
Price:$250.99 Image: http:///image.php?id=B000UFSL5G Best deal: http:///index.php?id=B000UFSL5G Neiko Tools USA 8-10 Gauge 2"~ 3-1/2" Air Framing Nailer Kit * Large Piston For Increasing Power * Stong and Light Weight Die-Cast Body * Drivers Full-Head Nails from 2" to 3-1/2" Long * Includes: Goggles, Oil, Wrenches and Instruction * Max Capacity: 40 pcs * 72-120 psi * Air Inlet: 1/4" NPT * 21 Degree Angle ...

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...

go to cell with date equaling TODAY()
Hello! I have a spreadsheet with January 01, 2006 thru December 31, 2006 in ascending order in column A, each date in a different cell (A1, A2, A3, etc.). I don't enter data into this sheet everyday..in fact, months could go by before having to enter an occurance for say, March 31. Is there a way to have excel, upon opening the spreadsheet, advance the cursor to the cell with that day's date in it? -- Thank you all for your help! Using function Date rather than Today() Works for me. Private Sub Workbook_Open() Dim r As Long Dim T As Long T = Date r = Application.Matc...

CRM Bulk Delete of Activities
We are new to CRM, and in the "testing" phase of implementation. Our marketing group has used smart lists and quick campaign which results in 30,000 open activies (emails). We can respond/delete/acknowledge them 250 at a time via the activities view. Clearly waiting for screen refreshes 120 times would waste a lot of time. Is there a way to "respond" to activies at a campaign level or in bulk? This is a multi-part message in MIME format. ------=_NextPart_000_0108_01C83679.362A3E60 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quot...

Emails moved from Sent Items to subfolder moving to Deleted Items instead
We have a problem where a couple of users are moving emails from their Sent Items to folders that are subfolders of their Inbox. However, random emails are moving to the Deleted Items instead. The users have no rules set up, and there is no limit on mailbox size for users. I know it's easy to dismiss them as having made a mistake, but one of the users is pretty competent and has in fact had this problem in the past. Has anyone experienced this problem? ...

Formatting cell for phone numbers
I am trying to format a cell with phone numbers so that it will look like the following: (937) 123-3456 (937) 129-9876 fax or (937) 123-3456 The word 'fax' is more specifically a label, not necessarily that word. I would like to have carriage returns between the numbers with the possibility of several numbers. This may be confusing and/or too complicated, but I'd like to give it a try. If anyone as suggestions or possible solutions, I would appreciate hearing them. Thanks, Craig >-----Original Message----- >I am trying to format a cell with phone numbers so that ...

Cell format options are truncated
When you click on any cell and click to format it, tabs do not display properly. Are you editing the cell at the time? You may only see the Font tab if that's the case. Make sure you're not in edit mode before you click on Format|Cells... maria.garciavelazquez@gmail.com wrote: > > When you click on any cell and click to format it, tabs do not display > properly. -- Dave Peterson ...

Help Please #8
Thanks For The Info : -- mikeee ----------------------------------------------------------------------- mikeeee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1461 View this thread: http://www.excelforum.com/showthread.php?threadid=26424 ...

cannot change part of a merged cell
Annoying problem... I merged cells and come time to paste from the clipboard into a merged cell, I get the error it can't be done. I can hit F2 and paste. It's an annoying step and users who are not quick on the draw with Excel would be stuck. Is there a way to stop the error and succeed with a paste? thanks! don't use office clipboard use Ctrl + C & Ctrl +V On Apr 7, 5:46=A0am, "shank" <sh...@tampabay.rr.com> wrote: > Annoying problem... > I merged cells and come time to paste from the clipboard into a merged cel= l, > I get the error it can...

selectively deleting cells
I would like to selectively delete alternate rows on an excel worksheet containing a long list of data. i.e. deleting rows/cells A3,A5,A7,A9,A11....A30573 etc. How do i do this quickly without having to use CTRL + select for each specific cell/row? Del, It isn't clear what you mean by "selectively" delete the rows. If you just want to delete every other row, use code like Sub DeleteEveryOtherRow() Dim RowNdx As Long Application.ScreenUpdating = False For RowNdx = 30573 To 3 Step -2 Rows(RowNdx).Delete Next RowNdx Application.ScreenUpdating = True E...