Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Work Number Field - show only phone numbers not extensions

  1. #1
    Board Regular
    Join Date
    May 2005
    Posts
    503
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Work Number Field - show only phone numbers not extensions

    I need two separate pieces of information based upon the phone number field, the phone number itself and the extension in a separate field. I hope asking 2 questions in 1 is okay - I apologize in advance if this is against forum rules.

    #1
    I have a report where I 'm trying to show work numbers without extensions in one field and the phone extension (if they have one) in another field.

    The phone number field has some fields with no phone number, some fields with this format "(###)###-####" and some field with this format "(###) ###-####"

    When I use the left function below I get a "Type#" error in the all these fields.

    Formula: Left([work_phone],14)

    What formula show I use to get (650)333-5555 (165) or (650) 333-5555 (165) to display (650)333-5555 or (650) 333-5555 in the report and have the null fields show nothing?

    #2

    Also, I would like to extract the extension number, if there is one, in the field next to it. Using the same example I would like to display 165 in a separate field

    Thank you in advance,

    Michael

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    8,565
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Work Number Field - show only phone numbers not extensions

    Hi, I can't replicate your problem.

    The LEFT() function in Excel should work reasonably well on both alpha and numeric entries.

    I can't recall seeing a "TYPE#" error in Excel.

    Are you actually using Excel, or perhaps some other spreadsheet package ?
    Or are you doing this analysis through VBA ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    Board Regular
    Join Date
    May 2005
    Posts
    503
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Work Number Field - show only phone numbers not extensions

    Thank you Gerald but I was trying to do this in Access, not Excel.

    The database we're using allows for phone extensions in the same field as the regular phone number. I need to find a method to prevent these extra characters from showing in the phone field. I don't know if I have my if statement set up properly or not.

    I tried using IIF with IsError function as the statement but it ended up not displaying any numbers even though I had the left argument above in the false condition of the statement. I don't know what I did wrong.


    Michael

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,491
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Work Number Field - show only phone numbers not extensions

    In the underlying table where these phone numbers are stored, can you tell us the following properties of this phone number field?
    - Data Type
    - Field Size
    - Format
    - Input Mask
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    May 2005
    Posts
    503
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Work Number Field - show only phone numbers not extensions

    I believe the data type is text. Yikes that mght be the problem.

    I think the field size is 255 and it is a default input mask.

    would the text field data type create the problem?

    I'm not at the office any won't get there for another few hours but I'll look at the this info when I get there.

    Michael

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,491
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Work Number Field - show only phone numbers not extensions

    Yes, please confirm those details, as those really hold the key as to what is going on and how to approach solving your issue.
    Note that Formats can only be applied to numbers, not text.

    Here is an some information that you may find interesting: http://www.utteraccess.com/forum/ind...post&p=1001227
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    8,565
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Work Number Field - show only phone numbers not extensions

    Apologies ! I hadn't noticed this was in the Access part of the forum - I found it in the "Unanswered posts" section. Sorry !
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,491
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Work Number Field - show only phone numbers not extensions

    I hadn't noticed this was in the Access part of the forum - I found it in the "Unanswered posts" section
    I assume that you mean the "Zero Reply Posts" listing.
    Note that all the way over on the right of each line, it shows which forum each thread is found in.
    Many people miss that (I did too, initially, when I started using that list!).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    Board Regular
    Join Date
    May 2005
    Posts
    503
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Work Number Field - show only phone numbers not extensions

    thank you Joe for the link. I items are entered like phone numbers not formatted as phone numbers. So it is entered as a text string. Like I mentioned, I'll double check the data type, format, size and input mask when I get to work. I know I set up the formulas properly. What I can't understand is why when I put in the IfError function nested in the IIF with the Left function, it hides all the phone numbers. Too bad there isn't something in Access like in Excel where you can walk through the formula (the Evaluate Formula item on the Formulas ribbon)

  10. #10
    Board Regular
    Join Date
    May 2005
    Posts
    503
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Work Number Field - show only phone numbers not extensions

    Whew! I thought that I had posted to the wrong forum!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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