Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
So in each cell that contains the word "Mature", you want to remove everything between the = sign and that word? –  Indrek Sep 25 '12 at 10:22
    
Yes, that's it :) –  user161284 Sep 25 '12 at 10:25
    
The split function and an if on the array within a macro is what you want! –  Dave Sep 25 '12 at 10:30

1 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.)

enter image description here

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.

share|improve this answer
    
If it helps clarify the solution, I can write up a detailed explanation of the formula, like I did here. –  Indrek Sep 25 '12 at 10:52
    
Thanks Indrek :) I'll test this as soon as I get to my other computer that has Excel. I'll report how it goes. –  user161284 Sep 25 '12 at 12:23
    
Works like a charm :) Thank you so much Indrek! –  user161284 Sep 26 '12 at 5:29
    
@user161284 You're welcome. Please feel free to mark my answer as accepted by clicking the green checkmark outline below it. –  Indrek Sep 26 '12 at 8:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.