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
1412 Views

Similar Articles

[PageSpeed] 47

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:

How do I separate address information from within an Excel cell?
I have a Christmas Card list that was created using a single cell for the whole address. Street, City, Zip/Postal etc. I need to split those components out into separate cells in order to do a proper mail merge. Is there any way to do this. I am not completely unfamiliar with macros but I if that is the solution I would require a fair bit of hand holding. Thanks S. If you actually used commas to separate the fields (and none of the fields contain commas), you could select the column and do: Data|text to columns delimited comma Remember to keep enough open columns to the right s...

Counting empty cells within a range of cells #2
A sheet can have a variable range of adjacent cells each containing the value =1 The range defines the perimeter of, or surrounds a group of empty cells however outside the perimeter, the remaining cells on the sheet are empty as well. The task is to count the empty cells that are surrounded by the perimeter of 1�s: To do this manually, I use the @countif (range=0) function however it requires that I go into the worksheet and select the range manually. I would like to do this by formula or macro. The knowns are: 1) The location of the start/end cell of the range 2) That the next cell in ...

Will un- & reinstalling Outlook'03 delete email & addresses?
I've been plagued with an Outlook problem for about a month. Settings are right. Test email works fine. All other internet related programs are fine. But Outlook gives me a "send/receive error" message as fast as I can hit "Send/Receive". If I keep it open just to check contacts or calendar it always crashes within 10 to 60 minutes. If I start it in safe mode it seems to work all right. I reinstalled and it worked for 30 minutes and then did the same thing. If I Uninstall first, and then Reinstall, will it help? Will I lose my contacts and emails? Help! Find...

Outlook 2003 won't let me delete msg in Outbox
I have an email in my outbox that has a 6 MB attachment. Well, for whatever reason, Outlook was having issues sending the email, so after several hours, I decided to delete it. Pressing the delete button does nothing, no error. Trying to permenantly delete it (shift delete) and it gives me the error message "The item could not be deleted. It was either moved or already deleted, or access was denied." It's a bizarre error. I'm only using Outlook as a pop3 email reader. There's no exchange on the backend. This is with Windows XP Professional. Any help woul...

New mail appearing as old deleted item
When new mail arrives in inbox an old long deleted item appears in place of the new mail showing in bold as new mail. The real new mail is not visible at all and is therefore lost. This problem also used to happen in Windows Mail so I bought Outlook 2007 but it still happens intermittantly. -- Kate What do you see in your Unread Mail folder? "Kate B" <KateB@discussions.microsoft.com> wrote in message news:D9A95DD7-CFC4-423F-9B2B-F8B72177E2CE@microsoft.com... > When new mail arrives in inbox an old long deleted item appears in place of > the new mail...

Display cell attributes/format
Is there any way of examining the cell attributes/formatting on a worksheet other than individually with Format | Cell? I would assume so but can't find it. Thanks Brian Tozer Not in versions after and including xl97. -- Regards, Tom Ogilvy Brian Tozer <briantoz@ihug.co.nz> wrote in message news:bs3261$duo$1@lust.ihug.co.nz... > Is there any way of examining the cell attributes/formatting on a worksheet > other than individually with Format | Cell? > I would assume so but can't find it. > > Thanks > Brian Tozer > > ...

WHY DID YOU DELETE MY POST?
All I did was ask for some help with massive amounts of spam coming to my exchange server. Please explalin to me why you deleted my post I really need some help with this issue. On Thu, 9 Jun 2005 12:21:03 -0700, "dath" <dath@discussions.microsoft.com> wrote: >All I did was ask for some help with massive amounts of spam coming to my >exchange server. Please explalin to me why you deleted my post I really need >some help with this issue. You want to look again, Seth and Will replied. Seth said: depending on your hardware, etc you can do this on the same server, ...

excel printing #8
how can I print pictures in excel sheet properly as it is getting printed thrice whereas it shows once in the print preview. I've never seen xl do this--but I don't print too many pictures using xl. But after verifying that the pictures are indeed correct (there aren't triplicates), I'd look at the printer driver. Do you have another printer you can try? If it works on that other printer, I'd visit the printer's web site and look for a new (or fresher) copy of the driver. (But this is just something to try!) yong wrote: > > how can I print > pictures in...

Formula Cell Error
Hi all, i have a problem with the following code, would appreciate if anyone can give me some help on this. Basically everything works fine except for this line: Cells(i, 12).FormulaR1C1 = "=SUM(J" & StartCount & ":J" & EndCount & ")" The cell will reflect "=SUM('J2':'J3')" instead of "=SUM(J2:J3)". --------------------------------------------------------------------------------------------- Sub Test() For i = 2 To 100 Step 1 Cells(i, 1).Select If Cells(i, 1).Interior.ColorI...

why does excel add 0's to a cell?
when I add numbers to a cell, excel adds tree zero's to the number. Is this a bug in excel? How can I fix this problem? Check the number format for the cell. Format it as General. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Kathryn" <Kathryn@discussions.microsoft.com> wrote in message news:174D91D7-B8A2-4058-9A80-C47D33D369E6@microsoft.com... > when I add numbers to a cell, excel adds tree zero's to the > number. Is this a > bug in excel? How can I fix this problem? Kathryn, If Chip's su...

How to read B-LOB data from Oracel DB using Pro*c
Hello, I'm trying to read B-LOB data in Oracle Database using Pro*c. I know how to read it with char mode. What I want to know is read it with binary mode. Are there any good person help me? Let me know if you have a good sample source. Thanks and have a nice day. ...

Html Tags for Specific Words in a Cell
Hi Everyone, I have problem putting up html tags to every specific word in a cell. Let's say this praise. [QUOTE]JUAN: Honey, bakit MALI ang pinaglalagyan mong DALIRI ng WEDDING RING natin? MRS: Siyempre para TERNO! Mali din naman ang NAPANGASAWA ko eh![/ QUOTE] The above is a conversation and I'd like to put "<b>" and "</b>" at the beginning and end to make the talking character's name in bold format when i uploaded it in the internet. I target this result: [QUOTE]<b>JUAN</b>: Honey, bakit MALI ang pinaglalagyan mong DALIRI ng WEDDI...

Merge Cells I can't find
I was trying to hide a column, and got a message that I couldn't, because cells in another column were involved. I don't see those cells, and "merge" is turned off. Any suggestions for finding them? Ed This quick macro will remove all merges on the current sheet: Sub RemoveMerges() Cells.UnMerge End Sub You can, of course define a range instead. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Ed Kearns" wrote: > I was trying to hide a column, and got a message that I couldn't...

Delete Administrators Mailbox
I have an administrators mailbox of 4.7GB - what repprcussions are there if I delete this ? would the option be to re-home the e-mail account first ?,then delete the msilbox -- William W Davidson Network Engineer On Tue, 7 Dec 2004 08:53:02 -0800, "William Davidson" <WilliamDavidson@discussions.microsoft.com> wrote: >I have an administrators mailbox of 4.7GB - what repprcussions are there if I >delete this ? > >would the option be to re-home the e-mail account first ?,then delete the >msilbox You want to find out what's in it first and then prevent i...

Deleting Records based on criterion
I know about advanced filters and filtering out records based on a criterion. But if I wanted to use the approach of deleting the records that don't match a criterion, how would I go about this in Excel? I would rather not use a macro for this unless its the only way. Thanks, Bruce After you do your Autofilter (or in-place Advanced Filter) only the rows to be deleted will be visible. Select these rows (you only need to select one column) and do an Edit, Delete Row. Click Ok to the confirming prompt. Only the visible rows will be deleted (Excel is pretty smart<g>) just as y...

Cell Formula reference to cell Based On third Cell Content
I want to build a formula in one cell that calls a second based on the numeric value in a third. Specifically, Say the frst cell is F10. I want it to look up the number in F1, and then if F1=1, F10=E9*something if F1=2, F10=D9*something if F1=3, F10=C9* something etc. ...

reference value in a cell
I have these values in sheets. Sheet2!A1 = 5 Sheet3!A1 = 7 Now, I put Sheet1!A1 = "Sheet2" which can be varied to "Sheet3" or "Sheet4" etc. User has to provide the name of the sheet here in Sheet1!A1. The problem is what formula to put in Sheet1!A3 so that it checks the value in Sheet1!A1 and depending on sheet name, takes the value of A1 of that sheet. Ofcourse it is wrong, but, something like this [Sheet1!A1]![A1] i.e. if I write Sheet1!A1 = "Sheet3", the value in Sheet1!A3 becomes =7. Any help is appreciated. Try this: =INDIRECT(A1&"...

Wrap text in cell
It seems that wrap text and autofit row width and row height have a limit. Any idea where to change this limit? I have quite a large text in a cell and when I do wrap text and autofit, then parts of the text get cut of. The text is still there, but it is not shown in the cell and it is cut off when printing. You can enter 32767 characters in a cell. In Excel 2003 and earlier, only 1024 of these will be visible or printed. I believe Excel 2007 will show 8192 characters. If you add an Alt + Enter linefeed every 100 or so characters you can increase this 1024 limit. ...

zero value in cell
I have a table of engineering calculations. The same formula in each cell operation on variables in the row and column headers. i use the MIN function to evaluate two formulas. Then I take the minimum result and operate on it outside of hte MIN function. I'm getting zero values displaying in my table. When I pick a particular cell, and then pick the formula icon in the editor, the dialog box displays the correct reults for each of the two items and the formula overall. Why don't these results show in my table? -- Arthur I'm guessing your numbers are actually Text....

Actively select cells/ranges/sheets and Paste Link
Hello: What I am trying to do is select the cells or range on a sheet that I am interested in, then paste link on a different sheet. All the selections should be variable (I mean not be hardcoded). Thanks, pl_hlp Something like this: Sub YetAnotherTry() Dim r1 As Range, r2 As Range Set r1 = Application.InputBox(prompt:="select copy area", Type:=8) ady1 = r1.Address Sheets("Sheet2").Select Set r2 = Application.InputBox(prompt:="select destination cell", Type:=8) ady2 = r2.Address Sheets("sheet1").Select Range(ady1).Select Se...

deleting a calendar series needs new option
When you have a series for a meeting appointment and after a year there is no longer a meeting your first impulse is to delete the meeting series from your calender. When you do this it deletes all past, present and future occurrances of the appointment. It would be really nice if the delete had an option to say from here forward so you can preserve history in the calender. Thanks ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in...

delete multiple range names
I have a workbook with multiple sheets. The workbook was built by someone else and has multiple range names that are no longer needed. How can I delete all range names to start fresh? Hi, In excel 2007 , go to Formulas, Name manager, choose the name to delet and click delete "mnsue" wrote: > I have a workbook with multiple sheets. The workbook was built by someone > else and has multiple range names that are no longer needed. How can I > delete all range names to start fresh? The following macro will delete all range names in the active workbook S...

Imported/merged .ics file into calendar... how to undo/delete?
Hello. I accidentally imported an .ics file into my main calendar and am wondering if there's anyway to remove the entries that came from this ics file from my calendar? Thanks. Add the modified time field to the view then sort by it - all the imported events should be together with the same modified time. -- -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about...

internal margin around text in a cell
Hi, all... Within a cell (or the spreadsheet as a whole), is there a way to specify the width of the "internal margin"? I know this can be set for text boxes (separately for top, bottom, right, left), but I have found no reference in Help for doing this in a cell... Where I use a double-line border above a cell, the top of the text actually touches the border. For one column of cells, "Wrap text" is checked, so effective row height is variable from one row to the next. So far, I've managed by manually adjusting each row height and formatting the text to be &q...

Cell does not calculate automaticaly
A cell contains a linked formula to other cells. The destination cell will only show the information from the source cell if the destination cell is "double clicked" and the OK icon is clicked. The Options / Calculations / "Automatic" option button is enabled. This problem does not occur with other files. The file with the problem is over 11 meg in size, where the other files are less than 6 meg. HELP! Hi do you see the text 'Calculate' in your statusbar and does hitting F9 help?. If yes it could be that you just have to many formulas to allow automatic ...