Format column as Phone Number

DaveyD

New Member
Joined
May 20, 2015
Messages
29
Hi, I receive large reports in csv with connection data
I import that report into Power Query in order to create many transformations
I have a column that contains 10 digits as text, representing a phone number
I need to format that as a phone number as such: (123) 456-7890
I can't figure out how to use Text.Format to do this - I keep getting an error that it expects a record or list
I was trying:
each Text.Format(_, "(###) ###-####")

This formatting has to be done during PQ transformation - it cannot wait to be done after loaded

Why is this not working and how can I format as a phone number?

Thanks,
David
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

billszysz

Active Member
Joined
Feb 26, 2014
Messages
355
Office Version
  1. 365
TelProper Format
2547890175(254) 789-0175
1234567890(123) 456-7890
0256900414(025) 690-0414


Rich (BB code):
Table.AddColumn(#"Changed Type", "Proper Format", each Text.Format("(#{0}) #{1}-#{2}", Splitter.SplitTextByLengths({3,3,4}) ([Tel]) ), type text)
 

DaveyD

New Member
Joined
May 20, 2015
Messages
29
Wow, this is absolutely amazing!
I was so close with my attempts - I too tried using SplitTextByLength just as you did, but I didn't know how to apply it to Text.Format
It took me a couple of minutes to understand what the ([Tel]) was doing.
Splitter.SplitTextByLength is a function, therefore they get parens after it to accept an argument, which is [Tel]

Thanks so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,327
Messages
5,547,273
Members
410,783
Latest member
sonnny
Top