Results 1 to 5 of 5
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    checking cells with custom formats (Excel2000)

    I am trying to cross-check 2 lists of phone numbers for a staff list.
    The lists come from 2 sources.
    I need only check that the last 10 digits match.
    Although one of the lists shows an 11-digit number, on inspection some of the cells actually contain just a 4-digit number. Those cells have a custom format assigned like 9975320####, 9921235#### etc.
    There are thousands of them.
    Is there an way I can access this format to find the prefixes?

    zeddy

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: checking cells with custom formats (Excel2000)

    You can get it in VB (custom function or sub) using the cells "NumberFormat" property and then extracting out that section.

    You can also get the whole string "as displayed" useing the cells "Text" property something like:

    <pre>Function GetDisplay(rng As Range)
    GetDisplay = rng.Cells(1).Text
    End Function
    </pre>


    so if you use the formula:
    =GetDisplay(A1)
    it will but the string in a cell as displayed.

    Steve

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: checking cells with custom formats (Excel2000)

    "You can also get the whole string "as displayed" useing the cells "Text" property"

    Er, how do I do that.

    zeddy

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: checking cells with custom formats (Excel2000)

    Brilliant!

    Many thanks for your first-class assistance.

    zeddy

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: checking cells with custom formats (Excel2000)

    I anticipated your request and added to my post before you responded back.

    I edited the post twice. I kept getting interuppted and tried to finish my thought and post, then I thought of something else, etc.

    Hope this helps,
    Steve

Posting Permissions

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