Macro to replace ONLY x's with blank

[JugglerJAF]

I have a customer data file in which some cells have been completed with x's and I need to replace any cell that contains x's with a blank.

I can easily do this when the cell contains a single x by using find and replace within the macro, but some customers have completed fields with xx or xxx or xxxx and so on - these x's may be in upper or lower case.

I can't just replace all instances of x (or X) with blank on partial cells as there will be company names or address details that will contain an x.

What I need is a way to identify cells that contain ONLY x character(s): so "x" would be replaced with blank as would "xx", "xxx" etc., but "XYZ Limited", "LOXX & Co." or "Essex" need to be left as is.

I can't even think where to begin on this. Anyone got any bright ideas or can point me in the right direction?

Thanks
JAF

 

[xenou]

VOG's code works fine for me - must be something about the Mac version (Mike Rickson could tell us!)

@Juggler
Sorry that I missed your instructions - I was thinking the x's would all be single x's.

Without VBA you might try:

1) Replacing all "xx" with nothing (partial matches allowed)
2) Replace all single "x" (whole matches only)

That should eliminate everything - but we assume there are no "Essexx" or "XXray" values! XX seems a rare character combination to me so it should work - or go with VOG's macro which won't have any such loopholes.

 

[Jazzed]

I think you are correct but I have no way of checking. Your version of my code should work in any version of Excel except MAC 2008.

Thanks Peter!

 

[JugglerJAF]

Try this

Sub DelX()
Dim c As Range
For Each c In ActiveSheet.UsedRange
    With c
        If Replace(LCase(.Value), "x", "") = "" Then .Value = ""
    End With
Next c
End Sub

Works a treat. Many thanks.
JAF