How to replace all numbers in Excel with "x"

Question

Hello

I have a huge excel sheet where all the numbers need to be replaced by "x" (without affecting the formulaes).

Next to the column of figures to be replaced, I inserted a blank column with this function:

=REPLACE(A1,1,10,"x")

I  then dragged it down the column and repeat it for each column of the spreadsheet.  

Is there a quicker way of doing this? 

(NB: I can't use macro on the firm's computers)

Thanks for your help

Answer 1

No problem at all - I'm glad to have helped.

Cheers,

Russ.

Answer 2

Good evening.

Are you trying to keep the numbers available as numbers, but displaying them as "x"s ?

If so, you could change the number format for the cells to display "x".  This will display them as "x" but still allow you to refer to the cells and perform calculations on them.

In the example below, the format of B3:B8 has been set to "x" as shown in the second snapshot.

The example also shows that column B is a calculation (formula shown in B2) but is displaying as "x".  C3:C8 is also a calculation based on the results in B3:B8, showing that the "x"s are still being treated as the numbers they are calculating.

I hope this helps.

Cheers,

Russ.

Answer 3

Thank you very much Russ, it worked perfectly!

Answer 4

Hi again.

When you get a moment, could you mark this post as answered, please? That would be much appreciated.

Cheers,

Russ.