filter by strikethrough

%3CLINGO-SUB%20id%3D%22lingo-sub-363951%22%20slang%3D%22en-US%22%3Efilter%20by%20strikethrough%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363951%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20filter%20an%20Excel%20file%2C%20showing%20those%20cells%20that%20contain%20strikethrough%20text.%20I%20cannot%20find%20a%20way%20to%20do%20this%2C%20even%20in%20Advanced.%20How%20should%20I%20do%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20Professional%20Plus%202013%20(32%20bit)%2C%20Win%2064%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-363951%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363960%22%20slang%3D%22en-US%22%3ERe%3A%20filter%20by%20strikethrough%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363960%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20are%203%20methods%20how%20to%20do%20that%26nbsp%3B%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3E%3CA%20href%3D%22https%3A%2F%2Ftrumpexcel.com%2Ffilter-bold-font-formatting-in-excel%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ftrumpexcel.com%2Ffilter-bold-font-formatting-in-excel%2F%3C%2FA%3E%3C%2FFONT%3E.%20Samples%20are%20for%20the%20bold%20text%2C%20but%20you%20case%20is%20identical.%20If%20Get.Cell%20first%20parameter%20will%20be%2023%20instead%20of%2020%26nbsp%3B%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3E%3CA%20href%3D%22https%3A%2F%2Fwww.mrexcel.com%2Fforum%2Fexcel-questions%2F20611-info-only-get-cell-arguments.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.mrexcel.com%2Fforum%2Fexcel-questions%2F20611-info-only-get-cell-arguments.html%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013880%22%20slang%3D%22en-US%22%3ERe%3A%20filter%20by%20strikethrough%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013880%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F98715%22%20target%3D%22_blank%22%3E%40C%20V%20Horie%3C%2FA%3E%26nbsp%3B1.%20Select%20any%20cell%20in%20the%20spreadsheet%2C%20then%20click%20the%20%22Home%22%20tab.%20Click%20the%20%22Find%20and%20Select%22%20button%20and%20choose%20%22Find%22%20from%20the%20menu%20that%20appears.%3CBR%20%2F%3E2.%20Click%20%22Options%2C%22%20then%20click%20the%20%22Format%22%20button.%3CBR%20%2F%3E3.%20Click%20the%20%22Font%22%20tab.%20Click%20the%20box%20next%20to%20%22Strikethrough%22%20to%20place%20a%20check%20in%20it%2C%20then%20click%20%22OK.%22%3CBR%20%2F%3E4.%20Click%20%22Find%20All%22%20and%20a%20list%20of%20every%20cell%20that%20contains%20strikethrough%20text%20will%20appear%20at%20the%20bottom%20of%20the%20Find%20and%20Replace%20window.%3CBR%20%2F%3E5.%20Click%20on%20the%20name%20of%20a%20cell%20in%20the%20list%20to%20jump%20to%20that%20cell%20in%20the%20spreadsheet.%20Click%20the%20%22Find%20All%22%20button%20again%20to%20refresh%20the%20list%20if%20you%20alter%20the%20text%20in%20a%20cell%20to%20remove%20the%20strikethrough.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013979%22%20slang%3D%22en-US%22%3ERe%3A%20filter%20by%20strikethrough%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013979%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20GET.CELL%20does%20what%20it%20says%20on%20the%20tin%3B%20it%20gets%20the%20properties%20form%20a%20%3CU%3Esingle%3C%2FU%3E%20cell%20only.%26nbsp%3B%20I%20tried%20giving%20it%20the%20entire%20column%20from%20the%20table%20as%20the%20reference%20but%20the%20function%20only%20returned%20a%20single%20value%20so%20it%20was%20no%20use%20for%20the%20FILTER%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20hidden%20helper%20column%20in%20the%20Table%20did%20the%20trick%20though.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014317%22%20slang%3D%22en-US%22%3ERe%3A%20filter%20by%20strikethrough%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014317%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Peter%20-%20yes%2C%20that's%20with%20helper%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1161007%22%20slang%3D%22en-US%22%3ERe%3A%20filter%20by%20strikethrough%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1161007%22%20slang%3D%22en-US%22%3EIndeed%2C%20it%20isnt%20filtering%2C%20so%20thats%20a%20hassle%20in%20big%20data%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013923%22%20slang%3D%22en-US%22%3ERe%3A%20filter%20by%20strikethrough%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013923%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333119%22%20target%3D%22_blank%22%3E%40AGARCIA3234%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20okay%2C%20but%20not%20exactly%20filtering.%20Assume%20you%20have%20multi-column%20range%20with%20few%20hundreds%20of%20rows%20and%20want%20to%20work%20only%20with%20few%20rows%20which%20are%20marked%20by%20strikthrough%20text%20in%20first%20column.%26nbsp%3B%20Find%20by%20Format%20only%20selects%20such%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I want to filter an Excel file, showing those cells that contain strikethrough text. I cannot find a way to do this, even in Advanced. How should I do it?

 

Excel Professional Plus 2013 (32 bit), Win 64


Thank you

6 Replies
Highlighted

Hi,

 

Here are 3 methods how to do that https://trumpexcel.com/filter-bold-font-formatting-in-excel/. Samples are for the bold text, but you case is identical. If Get.Cell first parameter will be 23 instead of 20 https://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html

Highlighted

@C V Horie 1. Select any cell in the spreadsheet, then click the "Home" tab. Click the "Find and Select" button and choose "Find" from the menu that appears.
2. Click "Options," then click the "Format" button.
3. Click the "Font" tab. Click the box next to "Strikethrough" to place a check in it, then click "OK."
4. Click "Find All" and a list of every cell that contains strikethrough text will appear at the bottom of the Find and Replace window.
5. Click on the name of a cell in the list to jump to that cell in the spreadsheet. Click the "Find All" button again to refresh the list if you alter the text in a cell to remove the strikethrough.

Highlighted

@AGARCIA3234 

That's okay, but not exactly filtering. Assume you have multi-column range with few hundreds of rows and want to work only with few rows which are marked by strikthrough text in first column.  Find by Format only selects such cells.

Highlighted

@Sergei Baklan 

So GET.CELL does what it says on the tin; it gets the properties form a single cell only.  I tried giving it the entire column from the table as the reference but the function only returned a single value so it was no use for the FILTER function.

 

A hidden helper column in the Table did the trick though.

Highlighted

@Peter Bartholomew 

Hi Peter - yes, that's with helper column.

Highlighted
Indeed, it isnt filtering, so thats a hassle in big data