Change phone format
Hi there, I have a phone field with the data type as text. How do I delete the "(" & ")" in the field? For example, the phone number is (603) 208-1101 and I need it to be 6032081101.
Thanks
Thanks
ASKER
I need to store the value as 6032081101. How do I do that?
If you change the 0 to a 1 the data really is stored as 6032081101, it just APPEARS as (603) 208-1101.
To prove it, enter a phone number using the input mask (with the 1 instead of the 0) and then go into the design of the table and remove the input mask. You will see that only the numbers are stored. If you add the input mask back in you will see the phone number displayed with the () again. The input maks with a 1 shows only cosmetic changes to your data, but does not effect how it is stored.
If you have existing phone numbers with () you want to remove do this.
Open the table and click in the phone number field, Select Replace from the Edit menu and in Find What type (
Leave Replace with blank, make sure Look In says the name of your Phone field and change Match to any part of field. Now click on replace all.
Do the same for ) and -
If you only want a plain text field remove the input mask completely from the field and just type the numbers.
If it is still not clear let us know.
Stephen
To prove it, enter a phone number using the input mask (with the 1 instead of the 0) and then go into the design of the table and remove the input mask. You will see that only the numbers are stored. If you add the input mask back in you will see the phone number displayed with the () again. The input maks with a 1 shows only cosmetic changes to your data, but does not effect how it is stored.
If you have existing phone numbers with () you want to remove do this.
Open the table and click in the phone number field, Select Replace from the Edit menu and in Find What type (
Leave Replace with blank, make sure Look In says the name of your Phone field and change Match to any part of field. Now click on replace all.
Do the same for ) and -
If you only want a plain text field remove the input mask completely from the field and just type the numbers.
If it is still not clear let us know.
Stephen
ASKER
The input masks works like a charm for NEW numbers but I need to change existing phone numbers stored in that field. Please note that the data type is currently text.
I'm assuming I need to do an update query but can't seem to get trim, lens, right in the correct combination.
I'm assuming I need to do an update query but can't seem to get trim, lens, right in the correct combination.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Joe,
The query works great!! Anyway, got another field where the phone is in this (603)208-1101. I just can't seem to get the query modified correctly, could you helpout?
Thanks!
The query works great!! Anyway, got another field where the phone is in this (603)208-1101. I just can't seem to get the query modified correctly, could you helpout?
Thanks!
UPDATE MyTable
SET Phone = Mid$(Phone,2,3) & Mid$(Phone,6,3) & Mid$(Phone,11,4)
WHERE Instr(1,Phone,"(")>0
Joe
SET Phone = Mid$(Phone,2,3) & Mid$(Phone,6,3) & Mid$(Phone,11,4)
WHERE Instr(1,Phone,"(")>0
Joe
ASKER
oops, dropping the last digit. any recommendations!!
The last digit from where? If it is the last digit in the phone, change it this way.
UPDATE MyTable
SET Phone = Mid$(Phone,2,3) & Mid$(Phone,6,3) & Mid$(Phone,11)
WHERE Instr(1,Phone,"(")>0
Joe
UPDATE MyTable
SET Phone = Mid$(Phone,2,3) & Mid$(Phone,6,3) & Mid$(Phone,11)
WHERE Instr(1,Phone,"(")>0
Joe
ASKER
Still happening. The phone number ends up 603208101 if the existing phone is (603)208-1101.
I'll post extra pts if you want them. Thanks again for your help!
I'll post extra pts if you want them. Thanks again for your help!
The last part should be 10 not 11.
Stephen
ASKER
As you can see, the last digit 1 is getting trimmed off. Please note that I am using the example provided in the testing.
ASKER
Got it! THanks!!!
ASKER
Help, can you help me with another phone #, 208-1101 needs to be converted to 2081101.
ASKER
Hi Joe, found the answer and below is some stuff that may be helpful from Microsoft's website, from article Q210537.
common use for the RemoveAlphas() function is to remove the parentheses, dashes, and spaces from a telephone number or a social security number field. For example, the following strings contain parentheses, dashes, and spaces:
"(206) 635-7050"
"206-635-7050"
"535-87-4529"
After you run the RemoveAlphas() function, these strings will look as follows:
"2066357050"
"2066357050"
"535874529"
1 Create a new module.
2 Type or copy the following function in the Module window:
Function RemoveAlphas (ByVal AlphaNum as Variant)
Dim Clean As String
Dim Pos, A_Char$
Pos = 1
If IsNull(AlphaNum) Then Exit Function
For Pos = 1 To Len(AlphaNum)
A_Char$ = Mid(AlphaNum, Pos, 1)
If A_Char$ >= "0" And A_Char$ <= "9" Then
Clean$ = Clean$ + A_Char$
End If
Next Pos
RemoveAlphas = Clean$
End Function
Save the module with any unique name.
How to Use the RemoveAlphas() Function in an Update Query
1 Create a new query based on the table with the phone number field.
2 Place the phone number field in the first column of the query grid.
3 On the Query menu, click Update.
4 In the Update To row, enter the following for a field named Phone: RemoveAlphas([Phone])
5 Run the query.
common use for the RemoveAlphas() function is to remove the parentheses, dashes, and spaces from a telephone number or a social security number field. For example, the following strings contain parentheses, dashes, and spaces:
"(206) 635-7050"
"206-635-7050"
"535-87-4529"
After you run the RemoveAlphas() function, these strings will look as follows:
"2066357050"
"2066357050"
"535874529"
1 Create a new module.
2 Type or copy the following function in the Module window:
Function RemoveAlphas (ByVal AlphaNum as Variant)
Dim Clean As String
Dim Pos, A_Char$
Pos = 1
If IsNull(AlphaNum) Then Exit Function
For Pos = 1 To Len(AlphaNum)
A_Char$ = Mid(AlphaNum, Pos, 1)
If A_Char$ >= "0" And A_Char$ <= "9" Then
Clean$ = Clean$ + A_Char$
End If
Next Pos
RemoveAlphas = Clean$
End Function
Save the module with any unique name.
How to Use the RemoveAlphas() Function in an Update Query
1 Create a new query based on the table with the phone number field.
2 Place the phone number field in the first column of the query grid.
3 On the Query menu, click Update.
4 In the Update To row, enter the following for a field named Phone: RemoveAlphas([Phone])
5 Run the query.
If you want to see the field as (603) 208-1101 but have it stored internally as 6032081101 then you need to make a small change to the Input Mask.
Design the table and select the Phone Number field. In the input mask which will be like this:
\(9999") "00090009;0;_
change to:
\(9999") "00090009;1;_
The change of 0 to 1 means the literal characters are displayed but not stored in the table. This means the () show in the table, but if the input mask is not present, i.e. in a form text box with no mask, you will only see the numbers.
You could of course remove the input mask but that is not as good.
Stephen