Cleaning up UK 44 Telephone Numbers

Question

I have six columns with telephone numbers in, some have the UK 44 number at the beginning, which would be the best way to remove only the 44 if present at the beginning/left? It is a large dataset even for Designer, so looking for the most efficient step, thanks.

IraWatt · Accepted Answer

Hey @datadabbler,

I would use a Formula Tool as it is more efficient 9/10 times compared to Regex. A formula like this could work:

IF Left([Phone Number], 2)="44" THEN Substring([Phone Number],2,Length([Phone Number])) ELSE [Phone Number] ENDIF

It checks if the first two characters are 44, If yes then shorten the string, if no then leave it.

One thing to note is I changed the column to text with the select tool before the formula.

The community has some quick and easy videos on formulas and the Formula Tool here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Writing%20Expressions

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com

DataNath · Accepted Answer

To tackle all 6 columns at once, the best way is with a Multi-Field Formula. For the 44 at the start, you can just use the TrimLeft() function - if the numbers don't start with 44 then they'll be ignored. If your numbers fields are numeric then you'll need to use the ToString() function too, as below:

ToNumber(trimleft(tostring([_CurrentField_]),'44'))

If they're already strings then you can just use:

trimleft([_CurrentField_],'44')