Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have a spreadsheet with a lot of numbers. I want to print the
spreadsheet to show someone, but I want all numbers to be x'ed out. For example, 123 becomes xxx and 1,234 becomes x,xxx. Is there an easy way to accomplish this? It would be ideal if I could do a find-and-replace. Alternatively, it would be okay if I could accomplish this with custom format. I copied the workbook and did a copy-and-paste-special-values so there are no formulas, which would otherwise get screwed up by changing numbers to text. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
if all you wnat to change everthing you can use find & replace putting ? in
the fnd and x in replace, or just use find and replace 10 times with a different number each time -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) " wrote: I have a spreadsheet with a lot of numbers. I want to print the spreadsheet to show someone, but I want all numbers to be x'ed out. For example, 123 becomes xxx and 1,234 becomes x,xxx. Is there an easy way to accomplish this? It would be ideal if I could do a find-and-replace. Alternatively, it would be okay if I could accomplish this with custom format. I copied the workbook and did a copy-and-paste-special-values so there are no formulas, which would otherwise get screwed up by changing numbers to text. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Mar 10, 1:34 pm, john wrote:
" wrote: I have a spreadsheet with a lot of numbers. I want to print the spreadsheet to show someone, but I want all numbers to be x'ed out. For example, 123 becomes xxx and 1,234 becomes x,xxx. Is there an easy way to accomplish this? if all you wnat to change everthing you can use find & replace putting ? in the fnd and x in replace Close! But "?" matches every character, including commas. I had said that I want 1,234 to become x,xxx. Is there a wildcard character that matches just digits? I tried "#", to no avail. Does the Find operation permit only the same special characters that SEARCH() does, namley "?", "*" and "~"? (I could not find any mention of wildcard characters in the Find Text and Numbers help page.) or just use find and replace 10 times with a different number each time I thought of that, too, but it's not practical for my spreadsheet. If I have to write a macro, so be it. But I was hoping to avoid that. |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Perhaps this will give you a head start in writing your macro. Definitely
use this on a COPY of your workbook, as it DESTROYS both FORMULAs and NUMERIC ENTRIES. Put your original in a very safe place (or make a special copy of it also) to prevent accidental loss of your work through a momentary lapse in processing sequence. I would make a copy, close the original, open the copy, put the code in it at the very least. Sub DigitsToX() Dim anyCell As Object Dim anyContent As Variant Dim LC As Integer Const Digits = "0123456789" ' 'WARNING: run on a copy of your workbook 'this process DESTROYS the content of the 'cells it works on, including 'erasing' 'any formulas ' Application.ScreenUpdating = False ActiveSheet.UsedRange.Select 'since destroying numeric values that 'other cell formulas are dependent on 'will result in a #VALUE error, first 'convert all formulas to their results. 'As mentioned - this destroys the formula 'pretty much the same as Edit | Paste Special w/Values ' For Each anyCell In Selection anyCell.Formula = anyCell.Value ' just do it! Next 'now you can safely convert digits to x's For Each anyCell In Selection If Not (IsEmpty(anyCell)) Then 'added test, use this to keep from 'x-ing out numbers in phrases 'as changing Page 99 Footnote 4 'to become 'Page xx Footnote x ' If IsNumeric(anyCell.Value) Then anyContent = anyCell.Value If Len(anyContent) 0 Then For LC = 1 To Len(anyContent) If InStr(Digits, Mid(anyContent, LC, 1)) Then Mid(anyContent, LC, 1) = "x" End If Next anyCell.Formula = anyContent End If ' Len() check End If ' check for numeric content End If ' Empty check Next Application.ScreenUpdating = True End Sub How long that takes to run depends on many things: speed of your system, size of the UsedRange on a worksheet, number of cells that work actually has to be performed in. By the way: I wouldn't consider just coming up with some format to hide the values - a simple selection of all the cells and Edit | Clear | Formats would instantly reveal the original values. " wrote: On Mar 10, 1:34 pm, john wrote: " wrote: I have a spreadsheet with a lot of numbers. I want to print the spreadsheet to show someone, but I want all numbers to be x'ed out. For example, 123 becomes xxx and 1,234 becomes x,xxx. Is there an easy way to accomplish this? if all you wnat to change everthing you can use find & replace putting ? in the fnd and x in replace Close! But "?" matches every character, including commas. I had said that I want 1,234 to become x,xxx. Is there a wildcard character that matches just digits? I tried "#", to no avail. Does the Find operation permit only the same special characters that SEARCH() does, namley "?", "*" and "~"? (I could not find any mention of wildcard characters in the Find Text and Numbers help page.) or just use find and replace 10 times with a different number each time I thought of that, too, but it's not practical for my spreadsheet. If I have to write a macro, so be it. But I was hoping to avoid that. |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Mar 10, 4:33 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote: Perhaps this will give you a head start in writing your macro. Thanks for the head start. By the way: I wouldn't consider just coming up with some format to hide the values - a simple selection of all the cells and Edit | Clear | Formats would instantly reveal the original values. Wouldn't be a problem. As I mentioned initially, I am only going to print what I intend to show. I am not giving anyone the file. |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
If they're all 3 or 4 digit numbers, you could try a custom number format of:
[999]"x,xxx";[0]"xxx";General " wrote: On Mar 10, 4:33 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Perhaps this will give you a head start in writing your macro. Thanks for the head start. By the way: I wouldn't consider just coming up with some format to hide the values - a simple selection of all the cells and Edit | Clear | Formats would instantly reveal the original values. Wouldn't be a problem. As I mentioned initially, I am only going to print what I intend to show. I am not giving anyone the file. -- Dave Peterson |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
If printing is all you need to do, then probably Dave's suggestion or a
variation of it depending on range of values you have, is a good way to go. Or even a variation of the code I put up earlier - instead of destroying formulas and actual values, just go to the IsNumeric() section and then set the cell's format to a custom one as Dave suggests. Probably best done with a copy also, since reverse engineering the format might be tedious if you have various numeric types on the sheets like General, Currency or other special number formats. " wrote: On Mar 10, 4:33 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Perhaps this will give you a head start in writing your macro. Thanks for the head start. By the way: I wouldn't consider just coming up with some format to hide the values - a simple selection of all the cells and Edit | Clear | Formats would instantly reveal the original values. Wouldn't be a problem. As I mentioned initially, I am only going to print what I intend to show. I am not giving anyone the file. |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Mar 10, 4:33 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote: ActiveSheet.UsedRange.Select [....] For Each anyCell In Selection What does ActiveSheet.UsedRange.Select do? It seems that "for each cell in selection" works without it. anyContent = anyCell.Value [...] For LC = 1 To Len(anyContent) If InStr(Digits, Mid(anyContent, LC, 1)) Then Mid(anyContent, LC, 1) = "x" End If Next anyCell.Formula = anyContent This is close to what I want to do. However, I lose certain things such as commas separating 1000s because that is part of the cell's numeric format, not the value. Because the numeric formats of the cells that I will apply the macro to vary in appearance (e.g. different number of decimal places, with and without "$", etc), I think the ideal logic would be something like this: dim content as string content = Display(cell.value) for i = 1 to len(content) 'replace digits with "x" next cell.formula = content What can I use for Display()? In other words, I am looking for a VBA (or worksheet) function whose result will be the cell value exactly as Excel would format it according to Format - Cells options (all of them). I don't think Format(cell.value) (without format expression) does not work for me. I think I would need a way to adapt the format expression (second argument) based on existing the cell format. Sounds hard. Caveat: When I say "exactly as Excel would format it", I am not sure what to expect if the cell alignment is Right Adjusted with some indentation applied. I would like the result to rely on the cell format for that aspect (indentation). |
#9
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Mar 10, 6:28 pm, Dave Peterson wrote:
If they're all 3 or 4 digit numbers, you could try a custom number format of: [999]"x,xxx";[0]"xxx";General When I tried to extend this concept, I get an error. For example: [9999]"xx,xxx";[999]"x,xxx";[0]"xxx";general Is this form of custom format limited to only two conditions? The Help page also shows only two conditions; but I do not see any mention of a limit. |
#10
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You've reached the limit.
" wrote: On Mar 10, 6:28 pm, Dave Peterson wrote: If they're all 3 or 4 digit numbers, you could try a custom number format of: [999]"x,xxx";[0]"xxx";General When I tried to extend this concept, I get an error. For example: [9999]"xx,xxx";[999]"x,xxx";[0]"xxx";general Is this form of custom format limited to only two conditions? The Help page also shows only two conditions; but I do not see any mention of a limit. -- Dave Peterson |
#11
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
..UsedRange is a block of cells starting at A1 to the column farthest right
you've probably ever used on the sheet, and on down to the highest numbered row. i.e., if at one time you had a value in cell Z1 and at some time had a cell in A44, used range would pick up from A1:Z44 This is also the 'marker' that often makes a workbook take up more space than need be because it doesn't reliably reset when you delete rows/columns. But by using it we guarantee that we get all cells on a sheet that might have something in them. The IsEmpty() test will filter out any without anything in them during the processing. Cells.Select could have been used also, but at the risk of selecting everything from A1:IV65536. Let me mess with the code a bit - probably tomorrow - it's 3:15 (new time) and I'm thinking of going to bed soon. I have an idea that may work to do most of what you want - or at least get things looking generally like most numeric values going for us: right justified. Text always left-justifies by default, numbers right justify. Doing additional testing for current cell format is going to add to the time to process - is that going to be a real issue? Again, time to complete is dependent on a lot of variables, and if you've already tried the code with real data, maybe you have an idea of how long it's taking. I'd say we could easily double or possibly even triple (or worse) the time to process by doing that kind of testing and formatting. " wrote: On Mar 10, 4:33 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: ActiveSheet.UsedRange.Select [....] For Each anyCell In Selection What does ActiveSheet.UsedRange.Select do? It seems that "for each cell in selection" works without it. anyContent = anyCell.Value [...] For LC = 1 To Len(anyContent) If InStr(Digits, Mid(anyContent, LC, 1)) Then Mid(anyContent, LC, 1) = "x" End If Next anyCell.Formula = anyContent This is close to what I want to do. However, I lose certain things such as commas separating 1000s because that is part of the cell's numeric format, not the value. Because the numeric formats of the cells that I will apply the macro to vary in appearance (e.g. different number of decimal places, with and without "$", etc), I think the ideal logic would be something like this: dim content as string content = Display(cell.value) for i = 1 to len(content) 'replace digits with "x" next cell.formula = content What can I use for Display()? In other words, I am looking for a VBA (or worksheet) function whose result will be the cell value exactly as Excel would format it according to Format - Cells options (all of them). I don't think Format(cell.value) (without format expression) does not work for me. I think I would need a way to adapt the format expression (second argument) based on existing the cell format. Sounds hard. Caveat: When I say "exactly as Excel would format it", I am not sure what to expect if the cell alignment is Right Adjusted with some indentation applied. I would like the result to rely on the cell format for that aspect (indentation). |
#12
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
See if you can live with this?
Sub DigitsToX() Dim anyCell As Object Dim anyText As String Dim LC As Integer Const Digits = "0123456789" ' 'WARNING: run on a copy of your workbook 'this process DESTROYS the content of the 'cells it works on, including 'erasing' 'any formulas ' Application.ScreenUpdating = False ActiveSheet.UsedRange.Select 'since destroying numeric values that 'other cell formulas are dependent on 'will result in a #VALUE error, first 'convert all formulas to their results 'as mentioned - this destroys the formula 'pretty much the same as Edit | Paste Special w/Values ' For Each anyCell In Selection anyCell.Formula = anyCell.Value ' just do it! Next 'now you can safely convert Digits to x's For Each anyCell In Selection If Not (IsEmpty(anyCell)) Then 'added test, use this to keep from 'x-ing out numbers in phrases 'as changing Page 99 Footnote 4 'to become 'Page xx Footnote x ' If IsNumeric(anyCell.Value) Then anyText = anyCell.Text If Len(anyText) 0 Then For LC = 1 To Len(anyText) If InStr(Digits, Mid(anyText, LC, 1)) Then Mid(anyText, LC, 1) = "x" End If Next anyCell.Value = anyText anyCell.HorizontalAlignment = xlRight End If ' Len() check End If ' check for numeric content End If ' Empty check Next Range("A1").Select Application.ScreenUpdating = True End Sub It fairly well preserves the format and layout except when the Accounting format has been chosen (that puts leading spaces between the $ and 1st digit to keep the $ symbol and decimal aligned in a column). The format looks the same when you examine it in VB as that for Currency where the $ is butted up against the 1st digit. So I can't determine when I need to put some spacing between the $ symbol and 1st digit. But the dollar sign, commas and decimal are preserved, and if format added a space at the right end, that's preserved also. I do a right-align to make it look like most numbers do, although we can probably do testing for other alignment and emulate it fairly easily. " wrote: On Mar 10, 4:33 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: ActiveSheet.UsedRange.Select [....] For Each anyCell In Selection What does ActiveSheet.UsedRange.Select do? It seems that "for each cell in selection" works without it. anyContent = anyCell.Value [...] For LC = 1 To Len(anyContent) If InStr(Digits, Mid(anyContent, LC, 1)) Then Mid(anyContent, LC, 1) = "x" End If Next anyCell.Formula = anyContent This is close to what I want to do. However, I lose certain things such as commas separating 1000s because that is part of the cell's numeric format, not the value. Because the numeric formats of the cells that I will apply the macro to vary in appearance (e.g. different number of decimal places, with and without "$", etc), I think the ideal logic would be something like this: dim content as string content = Display(cell.value) for i = 1 to len(content) 'replace digits with "x" next cell.formula = content What can I use for Display()? In other words, I am looking for a VBA (or worksheet) function whose result will be the cell value exactly as Excel would format it according to Format - Cells options (all of them). I don't think Format(cell.value) (without format expression) does not work for me. I think I would need a way to adapt the format expression (second argument) based on existing the cell format. Sounds hard. Caveat: When I say "exactly as Excel would format it", I am not sure what to expect if the cell alignment is Right Adjusted with some indentation applied. I would like the result to rely on the cell format for that aspect (indentation). |
#13
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Mar 11, 1:31 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote: anyText = anyCell.Text Yes, that is the key. Thanks. That was what I thought I should use, but I misread the Help page and did not even test my intuition. Argh! By the way, below is my final version. It worked in a blink of an eye -- although efficiency was never my concern. My rendition is clearly simpler than yours. Perhaps you could offer a critical review to explain any oversights due to my simplification. Thanks again. Your guidance is much appreciated. It fairly well preserves the format and layout except when the Accounting format has been chosen (that puts leading spaces between the $ and 1st digit My rendition of the macro has no trouble preserving the Accounting spacing without extra effort. I am using Office Excel 2003. PS: I am a little surprised that the macro works for negative numbers. All of my cells are formatted as Number with comma separator and some number of decimal places (varies). When I manually type - xxx,xxx into a cell, I get a #NAME! error. I have to prefix it with an apostrophe. Yet the macro has no problem changing -123,456 to - xxx,xxx. Go figure! Sub Xover() Dim cell As Object Dim val As String Dim i As Integer Dim n As String Application.ScreenUpdating = False For Each cell In Selection If IsNumeric(cell.Value) Then val = cell.Text For i = 1 To Len(val) n = Mid(val, i, 1) If "0" <= n And n <= "9" Then Mid(val, i, 1) = "x" End If Next cell.Formula = val End If Next Application.ScreenUpdating = True End Sub |
#14
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Nothing wrong with what you've done at all. Most people would chalk the
differences up to programming 'style'. I'm kind of a belt-and-suspenders coder and like to keep 'control' of what's going on in the code, too many years doing assembly language coding on life-critical systems, I suppose. You have an advantage in knowing what your data looks like and how your sheets are laid out. We here in these forums don't usually have that luxury, so you can get away with less error testing and "what if they have this situation?" checking. Initially I didn't catch on to the fact that you were providing printed copy only and thought you were really concerned with possibility of someone uncovering the real numbers and possibly even formulas in your workbook, so that's where converting formulas to values got started. Rather than un-write that, I just left it as was, since in the end even your method is 'destructive' in that you cannot recover the original contents of the cells. I also try to write code here that's pretty straight-forward and may even put in a couple of steps to show all of what's happening to the person requesting help. Keeping it simple (but longer) makes it more likely that they will be able to maintain it on their own if they have any VB knowledge at all. If I were going to be critical of anything you wrote, I'd recommend changing the name of variable 'val' There is a VB function VAL() that returns the numeric value of a string parameter. That kind of thing is risky at least, and potentially confusing or even fault-causing under the right circumstances. I'll also offer a recommendation, but I think you're probably already using it: Option Explicit This statement, at the beginning of the declarations section of a module, says that all variables/constants must be declared/defined before they are used. This was a big step forward in (V)Basic programming - it offers a method of helping you prevent logic errors through simple typo's of variable/constant names, and if you'll put all your variables/constants at the beginning of a routine or in their appropriate location (as Public Const in declarations of one module) it also affords you some code maintenance and management assistance. Glad we didn't need to go much deeper or give consideration to performance. I was concerned because a couple of things I've worked on through these forums lately have turned out to be very processing intensive - I have one that munches through around 1500 rows of data doing a lot of data manipulation that takes around 5 minutes to complete, on average, and on a fast dual-core system. Had another from another site that uncovered some issues in Excel charting - I thought the fact we were building 51 charts with 8800 data points each was the reason it was taking 11-12 minutes in Excel 2007 --- but other issues pushed me back to Excel 2003 where it only took about 1m40s for same job (charting engine differences). And I know these days people are kind of used to, or expect to see results instantaneously and a 3, 5 or 15 minute job is just a nightmare to them for some reason. I say "it takes as long as it takes" - enjoy the break, hit the coffee pot or water cooler, say Hi to your buddy in the next cubicle while waiting <g. " wrote: On Mar 11, 1:31 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: anyText = anyCell.Text Yes, that is the key. Thanks. That was what I thought I should use, but I misread the Help page and did not even test my intuition. Argh! By the way, below is my final version. It worked in a blink of an eye -- although efficiency was never my concern. My rendition is clearly simpler than yours. Perhaps you could offer a critical review to explain any oversights due to my simplification. Thanks again. Your guidance is much appreciated. It fairly well preserves the format and layout except when the Accounting format has been chosen (that puts leading spaces between the $ and 1st digit My rendition of the macro has no trouble preserving the Accounting spacing without extra effort. I am using Office Excel 2003. PS: I am a little surprised that the macro works for negative numbers. All of my cells are formatted as Number with comma separator and some number of decimal places (varies). When I manually type - xxx,xxx into a cell, I get a #NAME! error. I have to prefix it with an apostrophe. Yet the macro has no problem changing -123,456 to - xxx,xxx. Go figure! Sub Xover() Dim cell As Object Dim val As String Dim i As Integer Dim n As String Application.ScreenUpdating = False For Each cell In Selection If IsNumeric(cell.Value) Then val = cell.Text For i = 1 To Len(val) n = Mid(val, i, 1) If "0" <= n And n <= "9" Then Mid(val, i, 1) = "x" End If Next cell.Formula = val End If Next Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to change positive numbers to negative numbers without re-ente | Excel Worksheet Functions | |||
CHANGE A ROW OF POSTIVE NUMBERS TO NEGATIVE NUMBERS | Excel Discussion (Misc queries) | |||
Can I change a column of calculated numbers to absolute numbers? | Excel Discussion (Misc queries) | |||
change 2000 cells (negative numbers) into positive numbers | Excel Worksheet Functions | |||
How to change a series of positive numbers to negative numbers | Excel Worksheet Functions |