Work Number Field - show only phone numbers not extensions

bearcub

Well-known Member
Joined
May 18, 2005
Messages
701
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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 ?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Apologies ! I hadn't noticed this was in the Access part of the forum - I found it in the "Unanswered posts" section. Sorry !
 
Upvote 0
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!).
 
Upvote 0
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)
 
Upvote 0
Whew! I thought that I had posted to the wrong forum!
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,695
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top