RegEx to remove leading 1 from front of phone numbers :

K

Kara McConville

started a topic almost 7 years ago

We are trying to remove the leading 1 from the front of phone numbers, so that they just start with the area code.

For example, we get:

18885551234

We need:

8885551234

Is this possbile via a Regular Expression Dictionary?

A

Allison Bolduc

said almost 7 years ago

Kara - will this additional leading 1 be on all the phone numbers in the column?

Allison B.

A

Amanda Tetanich

said almost 7 years ago

Hi Kara,

I think I've got this one figured out for you, at least in 1 way. Someone else may reply with a better method, though!

The dictionary below does two steps, consecutively. First, it removes all non-digit characters from a phone number, such as parentheses and dashes. Then, it removes anything other than the last 10 digits of a phone number (area code + number). If you setup your dictionary exactly as it is displayed below, you should find success.

**Important notes**

1. If your original phone number has both an area code and an extension, this dictionary will not work properly. Because it counts backwards from the end of the phone number, you will lose any digits at the beginning beyond the 10 digits allowed by the expression. Depending on the type of phone numbers you are importing, this may or may not be a deal-breaker.

2. Since this dictionary is also stripping out the phone formatting, you will need to make sure your phone type is setup to have a default format. You can do this in Raiser's Edge under Config > Code Tables > Phone Types > double-click on the phone type to set the default format.

I've also attached a file that you can just copy and paste into a dictionary. Make sure to include the blank cells in Column A, because that is what is used to remove the extra elements in the phone number.

Phone.xlsx

I hope this helpful, and good luck!

-Amanda

A

Allison Bolduc

said almost 7 years ago

Here is another alternative one that might work well on business phones, it will strip any non-digits from the string, then remove the "1" if it is the first number in the string (will not remove other ones). I have also linked it here for easy copy/paste from excel: IgnoreFirstDigitif1.xlsx

Allison B.

How can we help you today?