LinkBack URL
About LinkBacks
Hi,
I need to change the phone number format in the "Phone" column to 999-122-1122.
In the data, I have (999)122-1212, 9991221212, and 999-122-1122.
Is there any easy way to do it in power query?
Thank you
Hi,
I need to change the phone number format in the "Phone" column to 999-122-1122.
In the data, I have (999)122-1212, 9991221212, and 999-122-1122.
Is there any easy way to do it in power query?
Thank you
PowerQuery won't change numbers in a column - it can only produce a copy and format it accordingly, so you might as well use a formula. With your phone numbers in A1 down, this in B1 copied down:
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")
Then format column B with the custom format: 000-000-0000
Alternatively, you could do Find & Replace three times on column A (for (, ) and -), then format column A as above.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Power Query
Please Login or Register to view this content.
And another slightly shorter PQ solution:
Please Login or Register to view this content.
Re: Change phone number format in power query
my 3 cents
Please Login or Register to view this content.
raw TI (999)122-1212 999-122-1212 999-122-1212 999-122-1122 999-122-1122
Please try
PHP Code:let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Ans = Table.AddColumn(Source, "Format", each Number.ToText(Number.From(Text.Select(Text.From([Column1]),{"0".."9"})),"000-000-0000"))
in
Ans
Thank you all for the help. Bo_Ry, your code worked perfectly.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks