AutoFilter phone number column while keeping phone number formatting

I have a table with columns of numbers in phone number "(222) 222-2222" and social security number "333-33-8888" formats in Excel 365. I need to be able to create cells or text buttons which will let users filter the data in those columns to just show those entries that "contain" their typed criteria (ie, if user types "8" and hits my Filter button above the phone column, all the rows with the phone number entry containing an "8" show up in the list.

Seems like a simple .autofilter task. I can make it work fine for text fields.

Of course, the problem is, these columns are formatted as numbers, and .autofilter only works on text fields. And ---- I need to keep the "(222) 222-2222" and "222-22-2222" appearance of the fields.

Any method I've used to convert the columns to text removes the phone number or ssn formatting. I get 2222222222 for a phone number--it loses the formatting.

I've tried a number of methods. For instance, sheet(1).NumberFormat = "at"* symbol or selection.TextToColumns, successfully convert those fields into searchable text -- but lose the phone/ssn formatting.

Can anyone give me any pointers on just what I need to do to get an .autofilter macro to work this way on a formatted column of numbers. I just can't figure out what is needed to get the text data that autofilter needs, while keeping the original number formatting (parenthesis, dashes, etc.)


* I had to type "at" instead of the symbol because this site misinterprets posts with that symbol as containing a link and won't let me, as a new member, post it(!)

This might be overly simple, but have you considered creating another column that converts the number value to text that can be used to filter on?

e.g. for ssn

Please Login or Register  to view this content.