Cleaning up UK 44 Telephone Numbers

[datadabbler]

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 solution]

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%20...

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[DataNath · Accepted solution]

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')

[datadabbler]

Both of these solutions worked perfectly, thank you. I went with the formula tool as it was quicker, but the Multi-Field Formula is a very neat solution, which I used for another problem I was having. Thanks both.