Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
clear all zero-length strings from spreadsheet?
Hi, I want a quick way of clearing all zero-length strings from a spreadsheet
I tried using Find & Replace Find: "" Replace: (left blank) But it put zeroes in instead of actuall deleting all contents. ?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
clear all zero-length strings from spreadsheet?
Not for me !
So you must have some sort of custom format on the cell Steve On Wed, 21 Feb 2007 14:11:06 -0000, Matt D Francis wrote: Hi, I want a quick way of clearing all zero-length strings from a spreadsheet I tried using Find & Replace Find: "" Replace: (left blank) But it put zeroes in instead of actuall deleting all contents. ?? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
clear all zero-length strings from spreadsheet?
If you have zero length strings in cells that are returned by formulae, then
to remove them enter and run: Sub clear_nothing() For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las) If Len(r.Value) = 0 Then r.Clear End If Next End Sub -- Gary''s Student gsnu200707 "Matt D Francis" wrote: Hi, I want a quick way of clearing all zero-length strings from a spreadsheet I tried using Find & Replace Find: "" Replace: (left blank) But it put zeroes in instead of actuall deleting all contents. ?? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
clear all zero-length strings from spreadsheet?
Thanks, but I can't see that makes a difference.
An example would be if you have a genuinly empty range of cells in Column B, and you enter a formula in B1 something like =IF(A1=10,"10+","") you'll get Col A = Col B 10 = 10+ (blank) = blank 5 = blank 15 = 10+ but the blanks in column B are actually zero length strings. If you copy&paste special, "values only" on column B, and sort A-Z, the "empty" cells will appear the top, whereas a genuinly empty cell wouldn't. If you then go Edit Clear All on those cells and re-apply the sort, they appear at the bottom. How can either get rid of those zero-length's or write formulas that produce a genuine null.? "john" wrote: Try tools/options/view tab/remove the tick from zero values -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "Matt D Francis" wrote: Hi, I want a quick way of clearing all zero-length strings from a spreadsheet I tried using Find & Replace Find: "" Replace: (left blank) But it put zeroes in instead of actuall deleting all contents. ?? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
clear all zero-length strings from spreadsheet?
Sorry, I mis read the origional question
If you use =IF(A34=10,"10+") you then get 'false' rather than a blank cell and then these will be sorted to the bottom of the list if that is any help? - John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "Matt D Francis" wrote: Can't see how that would work? An example would be if I use a formula like IF(A110,"Over","") The "" doesn't leave the cell empty, it leaves a zero-length string that appears before text if the column is sorted. How do I either write formulas that return a true null, or get rid of the zero-length strings? Edit Clear All works on a cell range where the but I want to be able to cover the whole spreadsheet in one go. ?? "john" wrote: Try tools/options/view tab/remove the tick from zero values -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "Matt D Francis" wrote: Hi, I want a quick way of clearing all zero-length strings from a spreadsheet I tried using Find & Replace Find: "" Replace: (left blank) But it put zeroes in instead of actuall deleting all contents. ?? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
clear all zero-length strings from spreadsheet?
Can't see how that would work?
An example would be if I use a formula like IF(A110,"Over","") The "" doesn't leave the cell empty, it leaves a zero-length string that appears before text if the column is sorted. How do I either write formulas that return a true null, or get rid of the zero-length strings? Edit Clear All works on a cell range where the but I want to be able to cover the whole spreadsheet in one go. ?? "john" wrote: Try tools/options/view tab/remove the tick from zero values -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "Matt D Francis" wrote: Hi, I want a quick way of clearing all zero-length strings from a spreadsheet I tried using Find & Replace Find: "" Replace: (left blank) But it put zeroes in instead of actuall deleting all contents. ?? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
clear all zero-length strings from spreadsheet?
One way is to change your equation to be
=IF(A1=10,"10+","**") use auto filter and select ** clear these cells a disadvantage is that you have to regenerate your equations whenever you update. There is no way to have a function respond in a true null. "Matt D Francis" wrote: Thanks, but I can't see that makes a difference. An example would be if you have a genuinly empty range of cells in Column B, and you enter a formula in B1 something like =IF(A1=10,"10+","") you'll get Col A = Col B 10 = 10+ (blank) = blank 5 = blank 15 = 10+ but the blanks in column B are actually zero length strings. If you copy&paste special, "values only" on column B, and sort A-Z, the "empty" cells will appear the top, whereas a genuinly empty cell wouldn't. If you then go Edit Clear All on those cells and re-apply the sort, they appear at the bottom. How can either get rid of those zero-length's or write formulas that produce a genuine null.? "john" wrote: Try tools/options/view tab/remove the tick from zero values -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "Matt D Francis" wrote: Hi, I want a quick way of clearing all zero-length strings from a spreadsheet I tried using Find & Replace Find: "" Replace: (left blank) But it put zeroes in instead of actuall deleting all contents. ?? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
clear all zero-length strings from spreadsheet?
Strange. They're just "General" as far as I know, I can't replicate it now
anyway! Try this in a new sheet, formatted "General" Column A Value 1 1 a a ="" ="" (leave blank) (leave blank) Column B Num 1 2 3 4 5 6 7 8 Now select A1:C9 and sort (with header row) on Value. You should get value num 1 1 1 2 5 6 a 3 a 4 7 8 Rows 5 & 6 , although appearing blank jump up the sort order to appear after numbers but before text, whereas the true blanks are left to the end. That's what I'm trying to avoid. Copy, Paste Special Values, doesn't help Find "", replace (blank) says value not found. ??????? Any suggestions? "SteveW" wrote: Not for me ! So you must have some sort of custom format on the cell Steve On Wed, 21 Feb 2007 14:11:06 -0000, Matt D Francis wrote: Hi, I want a quick way of clearing all zero-length strings from a spreadsheet I tried using Find & Replace Find: "" Replace: (left blank) But it put zeroes in instead of actuall deleting all contents. ?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clear all zero-length strings from spreadsheet? | Excel Discussion (Misc queries) | |||
clear all zero-length strings from spreadsheet? | Excel Discussion (Misc queries) | |||
How do I clear contents from a spreadsheet but not the formulas | Excel Discussion (Misc queries) | |||
Using control to clear spreadsheet | Excel Discussion (Misc queries) | |||
How do I export an Excel spreadsheet with fixed length rows? | Excel Discussion (Misc queries) |