+ Reply to Thread
Results 1 to 2 of 2

AutoFilter phone number column while keeping phone number formatting

  1. #1
    Registered User
    Join Date
    MS-Off Ver

    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(!)
    Last edited by Daneyuleb; 03-21-2021 at 11:36 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    MS-Off Ver
    Excel 2013

    Re: AutoFilter phone number column while keeping phone number formatting

    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.
    Last edited by Poizhan; 03-22-2021 at 12:57 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Phone number format - reference post (Formatting Textbox to type phone numbers only)
    By eddyrcabrera79 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-01-2015, 05:35 PM
  2. Is it possible to make Excel dial a phone number from my smart phone?
    By D_Step in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-19-2013, 06:11 PM
  3. Phone Number Formatting
    By jamz0770 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-21-2012, 02:47 AM
  4. phone number formatting
    By djbetterly in forum Excel General
    Replies: 2
    Last Post: 12-08-2009, 06:06 PM
  5. [SOLVED] PHONE NUMBER formatting
    By czbacnik@gmail.com in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-12-2006, 12:50 PM
  6. Formatting a Phone Number
    By Formatting Phone Number in forum Excel General
    Replies: 3
    Last Post: 04-06-2006, 03:30 PM
  7. [SOLVED] Phone Number Formatting
    By Brant Nyman in forum Excel General
    Replies: 5
    Last Post: 08-25-2005, 02:05 PM


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1