Too many times I am on site with a customer and I get an excel sheet from them that contains every user’s email address and DDI that is to be used for bulk user enablement in Skype for Business. 90% of the time the DDI is presented as you would dial it

Now this is fine but when I need to manipulate the excel sheet to format it ready for a powershell import I often find myself scratching my head thinking what excel formula do I need to use here.

So for my reference mainly and for anyone else who needs to refer to it, here it is

=SUBSTITUTE(“tel:+44″&RIGHT(B2,LEN(B2)-1)&”;ext=”&RIGHT(B2,6), ” “, “”)

As you can see the above formula correctly formats the Line URI to Skype for Business format and removes whitespaces found anywhere in the string.

Hope this helps you when in need.