Need to replace numbers with X

[BhanupriyaP]

I have the data, names Vs some numbers..large data. I want to replace all the numbers with "X". Currently am doing it manually. Is there any way, I can do it faster please? It's my weekly activity and very time consuming at the moment.

Thanks in advance.

Sample:

Sum of Amt		Months	ExpenseDate
		Apr	May	Jul	Aug	Sep
ApproverID	ClaimCurrency
XYZ	EUR	276​				88.35​
	EUR	755.14​		22.44​		172.35​
	GBP		636.88​		50​	570.34​
ABC	CAD		999.3​			2400.59​
	CAD					844.81​
UVW	USD					866.07​

Output:

Sum of Amt		Months	ExpenseDate
		Apr	May	Jul	Aug	Sep
ApproverID	ClaimCurrency
XYZ	EUR	X				X
	EUR	X		X		X
	GBP		X		X	X
ABC	CAD		X			X
	CAD					X
UVW	USD					X

 

[shrivallabha]

You probably will have to use VBA solution like below.

Public Sub ReplaceNumbers()
Dim rg As Range
For Each rg In ActiveSheet.UsedRange
    If IsNumeric(rg.Value) Then rg.Value = "X"
Next
End Sub

 

[BhanupriyaP]

Really appreciate your quick reply. Thanks! It helps!

 

[Fluff13]

Another option, without a loop

Sub Bhanupriya()
    ActiveSheet.UsedRange.SpecialCells(xlConstants, xlNumbers).Value = "X"
End Sub

 

[Peter Bartholomew]

@BhanupriyaP

Did you implement one of the VBA solutions?
If you are happier with a manual process, it is possible to perform the second approach manually:

Under Find & Select, bring up the 'Go To Special' dialogue box and set it to Constants-Numbers.
With all the cells selected, type "X" into the formula bar and commit with Ctrl+Enter.

This is not as satisfactory as the VBA solutions but it is far more reliable than identifying and changing the numbers individually.