Delete all cells except containing specific text

Hi,

I have a large excel file with thousands of columns.
I want to keep all column headers plus only those cells which contain specific word. Rest of the cells should be deleted so that I am only left with column header and required cells below.

Need your help guys.

filter your data

Originally Posted by carlmon

filter your data

You mean filtering each column if I am not wrong, but there are thousands of columns which is a challenge.

I filtered entire data set, then filtered one column to the specified word.
Now rest of the columns hide the required data.
As expected I guess.

Sorry for the delay, sample file is attached.

Here you can see two sheets, one is 'Before' which shows the raw data where I have to keep only those cells having the text '2G'.
Second sheet contains required data after cleaning done.

Hope it clarifies.

Originally Posted by jolivanes

For future explanations.
You don't have cells with text '2G'.
You have cells where the last two letters/numbers of the cell values = 2G.
Makes all the difference if you don't have an example workbook.

Yes, in most of the cases '2G' is the last two letters of the cell value. But it can be elsewhere as well.
So the key is the these two alphabets coming together in any cell warrants that cell to be kept and rest to be deleted.

Originally Posted by jolivanes

Re: "Yes, in most of the cases '2G' is the last two letters of the cell value. But it can be elsewhere as well."
Not in your attachment. There it only shows at the end.
An attachment should be a representative workbook that has ALL possibilities so helpers don't have to do things over and over because things are being added/changed after.

I would be more than happy if I get any solution based on the scenario shown in attachment.

Originally Posted by jolivanes

Smart move. Telling possible helpers that after spending their hard to come by free time on a solution that will need to be changed later.

I literally can't understand what is the problem here with you??? Your replies are spam and are not helping anyone.

I have repeated multiple times that whatever people can support will be much appreciated since they are helping me for free and using their precious time.

I am indebted for their time and not putting any conditions here.

Originally Posted by JeteMc

This formula-based proposal converts the data on the Before sheet into a table.
On the after sheet the formula used to populate A2:F5 is:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Thanks a lot for your time and effort! Much appreciated.

Just a query, what if I want to add more columns since this seems to be working fine till column F? For additional columns, it is only returning first match.

Originally Posted by JeteMc

Please upload a sample file that illustrates the issue of only returning the first match for the additional columns.
Let us know if you have any questions.

Thanks a lot I made it to work, just needed drag and drop in additional columns!!