Macro to replace ONLY x's with blank

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
271
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
Sure - find and replace has an option to "match entire cell contents" - that's what you want, I think. Also, just select the column or data area you want to replace in, rather than the whole sheet (to be safe). And pick "Values" as the search option for "Look In".

You can do a case sensitive search too but I don't think that applies here...

ξ
 

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
271
Sure - find and replace has an option to "match entire cell contents" - that's what you want, I think. Also, just select the column or data area you want to replace in, rather than the whole sheet (to be safe). And pick "Values" as the search option for "Look In".

You can do a case sensitive search too but I don't think that applies here...

ξ

Thanks for the response. While I could do a find and replace on entire cell contents, I'd have to do that a whole bunch of times to pick up all the possible permutations of just x's (x, xx, xxx, xxxx, xxxxx, xxxxxx, xxxxxx etc.). The permutations of x in a cell could be anywhere from 1 to 40 and I'm sure there has to be a better way than doing 40 find/replace operations (especially considering that I could have over 20,000 rows of data and need to check each row across 6 columns)!!

What I'm after is a way to identify cells that ONLY contain x's and to replace those with blank, while not changing cells that contain an x along with any other letters, so x (or xx, xxx, xxxx, xxxxx, xxxxxx, xxxxxxx, xxxxxxxx etc.) would be replaced with a blank, but "Fox's Biscuits" would remain unchanged as although it contains an x it contains other letters as well.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this

Code:
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
 

Jazzed

Board Regular
Joined
Dec 8, 2009
Messages
61

ADVERTISEMENT

Shouldn't that be...?

Code:
If WorksheetFunction.Substitute(LCase(.Value), "x", "") = "" Then .Value = ""
 

Jazzed

Board Regular
Joined
Dec 8, 2009
Messages
61

ADVERTISEMENT

Why not use the VBA function Replace?

When I run your code, 'Replace' is highlighted and the following message appears...

"Compile Error:

Sub or Function not defined"
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top