• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to replace numbers with X

BhanupriyaP

New Member
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 AmtMonthsExpenseDate
AprMayJulAugSep
ApproverIDClaimCurrency
XYZEUR
276​
88.35​
EUR
755.14​
22.44​
172.35​
GBP
636.88​
50​
570.34​
ABCCAD
999.3​
2400.59​
CAD
844.81​
UVWUSD
866.07​
Output:
Sum of AmtMonthsExpenseDate
AprMayJulAugSep
ApproverIDClaimCurrency
XYZEURXX
EURXXX
GBPXXX
ABCCADXX
CADX
UVWUSDX
 

Fluff13

Active Member
Another option, without a loop
Code:
Sub Bhanupriya()
    ActiveSheet.UsedRange.SpecialCells(xlConstants, xlNumbers).Value = "X"
End Sub
 

Peter Bartholomew

Well-Known Member
@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.
 
Top