Phone number formating

[colinharwood]

Hi
I have a userform which places a phone number into a sheet, I would like to enter the number without spaces and have it automatically formatted when entered into the sheet. My problem is some numbers need to be formatted as 020 8765 1234 and some in the format 01777 123456. All numbers will either start 01 or 02. Is this possible please.
Thanks

 

[sandy666]

use XL2BB to post representative example of source data and expected result (not a picture!)

 

[sandy666]

example

src		Result
01234567890		01234 567890
02 457 65 98 12		024 7659 9812
01 4567 657 89		01456 765789

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Replace = Table.ReplaceValue(Table.TransformColumnTypes(Source,{{"src", type text}})," ","",Replacer.ReplaceText,{"src"}),
    Result = Table.AddColumn(Replace, "Result", each if Text.Start([src], 2) = "01" then Text.Start([src], 5)&" "&Text.End([src], 6) else Text.Start([src], 3)&" "&Text.Middle([src], 4, 4)&" "&Text.End([src], 4)),
    TSC = Table.SelectColumns(Result,{"Result"})
in
    TSC

 

[colinharwood]

Book1
	C	D
4	Source	result
5	2087771234	020 8777 1234
6	1789123456	01789 123456
Sheet1

 

[sandy666]

your source starts with 2 or 1 not 02 or 01
anyway if I add 0 at the start of the string the result is

src		Result
01234567890		01234 567890
02 457 65 98 12		024 7659 9812
01 4567 657 89		01456 765789
02087771234		020 7771 1234
01789123456		01789 123456

so assumption: your source column should be formatted as text before enter the phone number

 

[colinharwood]

what is changing the format between src & result

 

[sandy666]

source column should be as text, the result is as text also
phone "number" format is changed by M code as you can see in post #3

I suggest to update your profile (Account details) about Excel version and OS

 

[colinharwood]

Thanks for that

 

[sandy666]

You are welcome