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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

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,663
Messages
5,549,292
Members
410,908
Latest member
Allen P
Top