Reformatting A Phone Number In Access

I'm in the process of uploading data into an access database, and some of the data has phone numbers listed in the format of 9999999999 and others are listed as 999-999-9999. I wou

Solution 1:

This SQL function should work, replace [wireless num] with your field name:

test: IIf(Mid([wireless num],4,1)="-",Left([wireless num],3) & Mid([wireless num],5,3) & Right([wireless num],4),[Wireless Num])

If the 4th character is a -, that means it is undesired. In that scenario, it extracts each section of the phone number as substrings, concatenates them, and displays them as one string.

If the 4th character is not a -, we can assume the phone number is in the desired format.

---

This should also work:

Replace([wireless num],"-","")

It searches the Wireless Num field for -, and replaces it with a zero length string.