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.

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.

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.

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.

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.

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

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.

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).

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.

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

..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).

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).

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

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