Remove Extra Characters in Phone Field

  • Thread starter Thread starter Brigham Siton
  • Start date Start date
B

Brigham Siton

We have a telephone field that is in the following format:

800/123-1234

Is there a way to remove the extra characters and leave just the actual
number?

Thank you very much in advance.
 
Brigham,
Permanently?
Is every record in the same format?
And all have 10 digit numbers?

Run an Update Query.
Update YourTable Set YourTable.PhoneField = Left([PhoneField],3) &
Mid([PhoneField],5,3) & Right([PhoneField],4)

800/123-1234 becomes 8001231234

If you want 800 123 1234 use:
= Left([PhoneField],3) & " " 7 Mid([PhoneField],5,3) & " " &
Right([PhoneField],4)
 
Yes. All the records are like that.

I'll give that a shot. Thank you.


Fredg said:
Brigham,
Permanently?
Is every record in the same format?
And all have 10 digit numbers?

Run an Update Query.
Update YourTable Set YourTable.PhoneField = Left([PhoneField],3) &
Mid([PhoneField],5,3) & Right([PhoneField],4)

800/123-1234 becomes 8001231234

If you want 800 123 1234 use:
= Left([PhoneField],3) & " " 7 Mid([PhoneField],5,3) & " " &
Right([PhoneField],4)


--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Brigham Siton said:
We have a telephone field that is in the following format:

800/123-1234

Is there a way to remove the extra characters and leave just the actual
number?

Thank you very much in advance.
Click to expand...
Click to expand...
 
I'm working on a complex version of this very issue. To
do what you need (assume the table name is "test" and the
field name is "phone" in this exammple), then all you
need is something that looks like this in the "Field" row
of your query:

Expr1: Left([test]![phone_no],3) & Mid([test]!
[phone_no],5,3) & Right([test]![phone_no],4)

This will give you 8001231234 as a result. The "Mid"
function tells you which character to start with and how
many to use (in this case, 3). Replace the "Expr1" with
any name you would like to call the field in the results.

Good luck.
 
Back
Top