Regex Powershell Script to Change Phone Numbers

Hi,

I am trying to input the Phone Numbers from our Phone System into AD for OCS in E.164 Format. I already have an employee ID link between the systems so the update should be straight forward but I am having real problems working with Regex.


I found the following extract from the website below that I have tried to use but I can't find a way to use the different sections $1,$2 etc in powershell I can only see examples of using -replace

http://blogs.pointbridge.com/Blogs/schertz_jeff/Pages/Post.aspx?_ID=26 
##
## Normalize all AD phone numbers to E.164
##
\+?[\s()\-\./]*1?[\s()\-\./]*\(?\s*(\d\d\d)\s*\)?[\s()\-\./]*(\d\d\d)[\s()\-\./]*(\d\d\d\d)[\s]*
+1$1$2$3

Now if we dissect the entire rule, it's much easier to understand exactly what it is doing each step of the way:

EXPRESSION ACTION
\+? Ignore the first character if it is a +
[\s()\-\./]* Match any immediately following characters if they are a space ( ) dash or period
1? Ignore the next character if it is a 1
[\s()\-\./]* Match any immediately following characters if they are a space ( ) dash or period
\(? Ignore the next character if it is an open parenthesis
\s* Ignore any number of repeated spaces
(\d\d\d) Capture the first 3 digits and store as the first variable.
s* Ignore any number of repeated spaces
\)? Ignore the next character if it is a closed parenthesis
[\s()\-\./]* Match any immediately following characters if they are a space ( ) dash or period
(\d\d\d) Capture the next 3 digits and store as the second variable.
[\s()\-\./]* Match any number of immediately following characters if they are a space ( ) dash or period
(\d\d\d\d) Capture the last 4 digits and store as the third variable.
[\s]* Ignore any number of repeated spaces

+1$1$2$3
   
+1 Insert +1 into the translation pattern
$1 Insert the value of the first captured variable
$2 Insert the value of the second captured variable
$3 Insert the value of the third captured variable

There are some standards in the Phone Book but the following formats are valid

No Spaces
02071111111

Spaces
020 7111 1111

Characters
020-7111-1111
020.7111.1111
020~7111~1111

With International Number
+44 020 7111 1111
+44 (0) 20 7111 1111

The format of all the numbers should be e.164 standard which would make this
+442071111111


Although I understand regex at a very basic level I am struggling how to properly implement this via powershell. Through trail and error I have come up with the following which seems to work but was basically created through adding extra -replace statments after each run to get as many as possible in the correct format and I am sure there is a better way to do this.










$PSData = Import-Csv $PSFile
$Phone = $PSData | Where {$_.phoneoffice.length -ge 10}
$Phone | foreach {
$PhoneOfficeE164 = $_.phoneoffice -replace " " , "" -Replace "\(0\)", "" -replace "^\+?\44?\(?\0?\)?", "+44" -replace "\+440","+44" -replace "\." , "" -replace "\~","" -replace "\+4444","+44" -replace "\+44?0", "+44" -replace "\-",""
$PhoneWorkMobileE164 = $_.PhoneWorkMobile -replace " " , "" -Replace "\(0\)", "" -replace "^\+?\44?\(?\0?\)?", "+44" -replace "\+440","+44" -replace "\." , "" -replace "\~","" -replace "\+4444","+44" -replace "\+44?0", "+44" -replace "\-",""
$OffE164Val = $PhoneOfficeE164 -match "^(\+44[1|2|7]\d{9})"

			IF ($OffE164Val -eq "True" )
				{
				$_ | Add-Member noteProperty PhoneOfficeE164 $PhoneOfficeE164 -Force
				}
			ELSE
				{
				$_ | Add-Member noteProperty PhoneOfficeE164 $null -Force
				}
$MobE164Val = $PhoneWorkMobileE164 -match "^(\+44[7]\d{9})"

			IF ($MobE164Val -eq "True" )
				{
				$_ | Add-Member noteProperty PhoneWorkMobileE164 $PhoneWorkMobileE164 -Force
				}
			ELSE
				{
				$_ | Add-Member noteProperty PhoneWorkMobileE164 $null -Force
				}

}

Open in new window

LVL 2
pubeheedAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris DentPowerShell DeveloperCommented:

Hmm do you need so many replaces?

I can deal with the samples you've posted using this.

Test.txt is simply a list of your numbers above, it contains:

02071111111
020 7111 1111
020-7111-1111
020.7111.1111
020~7111~1111
+44 020 7111 1111
+44 (0) 20 7111 1111

HTH

Chris
Get-Content Test.txt | %{
  $PhoneNumber = $_
  # Replace leading 0, or +44 0 or +440 and leave +44 only
  $PhoneNumber = $PhoneNumber -Replace "(^\+44(\s)?0)|(^0)", "+44"
  # Drop (0)
  $PhoneNumber = $PhoneNumber -Replace "\(0\)"
  # Drop any character that isn't a number or +
  $PhoneNumber -Replace "[^\d^+]"  
}

Open in new window

pubeheedAuthor Commented:
Excellent Chris - this does exactly what I need and is much more robust compared to what I was doing.

I have 1 last question there is another potential number format that I have since discovered is valid:
(020 7111 1111)

The code above translates this to
02071111111
Chris DentPowerShell DeveloperCommented:

Hmm perhaps we should alter the order a little.

This has the advantage of making it shorter, we can lose a couple of the replace conditions by dropping the unwanted characters first.

Chris
Get-Content Test.txt | %{
  $PhoneNumber = $_
  # Drop any character that isn't a number or +
  $PhoneNumber = $PhoneNumber -Replace "[^\d^+]"
  # Replace leading 0 or +440 and leave +44 only
  $PhoneNumber = $PhoneNumber -Replace "(^\+440)|(^0)", "+44"
  $PhoneNumber
}

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pubeheedAuthor Commented:
Excellent Chris - I made one change to account for users also putting in the format 0044 so it goes like this:

Get-Content Test.txt | %{
  $PhoneNumber = $_
  # Drop any character that isn't a number or +
  $PhoneNumber = $PhoneNumber -Replace "[^\d^+]"
  # Replace leading 0 or +440 and leave +44 only
  $PhoneNumber = $PhoneNumber -Replace "(^\+440)|(^0044)|(^0)", "+44"
  $PhoneNumber
}
pubeheedAuthor Commented:
Thanks again Chris - I really do owe you a beer!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Server OS

From novice to tech pro — start learning today.