Replacing a certain string after a certain character in Excel

Question

I'm organizing my Mame files in a way that all the games marked with mature (meaning adult content) are filtered out. I have a text file in Excel (one game per line) and I'd like to accomplish the following:

Original line: 4psimasy=Tabletop / Mahjong *Mature*

Target line: 4psimasy=Mature

How could I do this?

Answer 1

Assuming your game is in A1, something like this should do the trick:

=IFERROR(REPLACE(A1; SEARCH("="; A1) + 1; SEARCH("Mature"; A1) - SEARCH("="; A1) - 1; ""); A1)

(Note: depending on your regional settings, you may have to replace the semicolons in the formula with commas.)

The REPLACE() function removes (by replacing with an empty string "") a given number of characters in the original string (A1) from the given starting position (right after the = sign).

If A1 doesn't contain the word "Mature" (or the = sign, for that matter), Excel throws the #VALUE error, which the IFERROR() function catches and returns the contents of A1 instead.

---

After the above, you can hide and lock the column with the original names, or copy the censored names and choose "Paste Special..." → Values to completely replace the original names. Or if your desired output is a text file, you can also move the censored names to a separate worksheet and export only that from Excel.