Using Wildcards

[morganallen]

Hi,


I have a spreadsheet full of data and I want to replace some cells with blanks but not all of them.

Essentially, anything with the word "blah" in it, I want to delete if not leave it unchanged (or call the cell back.)

IE - if A1 = Excel and A2 = Excelblah then A1 would remain unchanged while A2 was deleted.

Any thoughts on how to do this?

 

[pgc01]

Hi
Welcome to the board

Try:

- select the area of the woksheet where you want to perform the replacements

- use Replace with

Find what: *blah*
Replace with: (leave empty)

 

[morganallen]

Hey, thanks for the tip, but that isn't quite what I am trying to do.

For example https://blog.mrexcel.com and I search for mrexcel and replace it with (blank) it returns https://blog..com. What I want it to do is erase the cell completely.

Something along the lines of this logic: If cell contains xyz, erase cell. If not, return value of cell.

 

[West Man]

Try: =IF(ISNUMBER(SEARCH("blah",A8,1)),"",A8)

 

[mikerickson]

pgc01's method worked for me.

All cells that had blah as a sub-string were emptied.

What happened when you tried it and what did you want instead?

 

[morganallen]

awesome! that works!

thank you good sir

 

[morganallen]

pgc01's method worked for me.

All cells that had blah as a sub-string were emptied.

What happened when you tried it and what did you want instead?

if I have a cell "blahorg"

If I do the replace blah with (nothing), it will keep "org" in the cell when I want the entire cell erased.

Try: =IF(ISNUMBER(SEARCH("blah",A8,1)),"",A8)

That worked for what I was looking for.

 

[mikerickson]

Hmm. the behaviour you desire is what happened for me using Search and Replace, i.e. lookfor blah* and a cell with myblahcell ends up empty. (Excel 2010)

I'm glad you found a solution.

 

[pgc01]

if I have a cell "blahorg"

If I do the replace blah with (nothing), it will keep "org" in the cell when I want the entire cell erased.

Hi again

I did not post replace

blah

with nothing, what I posted was replacing

*blah*

with nothing.