Need to format telephone numbers into 10 digit number only.

[tamwink]

How do I remove the country codes, hyphens, and "+1" from a column of data?
All information appreciated.

 

[Fluff]

That formula will not "Change" anything, it will simply take the value in A2 & show a modified version in the cell with the formula.
Dies it give you the correct value, as shown in post#4?

 

[sandy666]

could you post more example data? eg. 50 lines?

 

[tamwink]

CITY​	STATE​		PHONE​
Whiting	NJ		‭+1 (704) 806-3506‬
Kendall Park	NJ		024 0608 8588
Kendall Park	NJ		030 377 1554
Kendall Park	NJ		06-687 5888
Kendall Park	NJ		0757 7192169
Lakewood	NJ		08503 810221
Lakewood	NJ		0910 830 3999
Lakewood	NJ		0917 535 9028
Lakewood	NJ		0929 035 6104
Whiting	NJ		095 935 6565
Whiting	NJ		1 512 473-2305
Whiting	NJ		1 585-258-3696
Whiting	NJ		1-(626) 539-0548
Beaufort	SC		p:+1(602) 7054282
Allendale	SC		p:+1(612) 750-9273
Sumter	SC		p:+1(770) 362-2000
Atlanta	GA		p:+1(979) 665-5052
Atlanta	GA		p:+117579209003
Atlanta	GA		p:+118014727251
Salt Lake City	UT		954 793-1073
Salt Lake City	UT		954 805-0608
West Valley City	UT		954 805-6295
Draper	UT		954 805-7107
s.l.c.	UT		954 812-9849
Salt Lake City	UT		954 815-4878
Vernal	UT		954 815-5773
Salt Lake City	UT		954 817-7959
Salt Lake City	UT		954 821-3795
Bountiful	UT		954 821-4484

 

[sandy666]

or try Power Query (Get&Transform)

raw	gaaap	PhoneNumbers
17206292779,		7206292779
1-678-416-8080,		6784168080
1(334) 662-9740,		3346629740
p:+117579209003		7579209003

// ExtractPhoneNumber
let
    Text = Table.AddColumn(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "PhoneNumbers", each Text.Combine(List.RemoveItems(Text.ToList([raw]),{" ","a".."z","-",".","+","(",")",",",":"}))),
    Result = Table.SelectColumns(Table.TransformColumns(Text, {{"PhoneNumbers", each Text.End(_, 10), type text}}),{"PhoneNumbers"})
in
    Result

 

[tamwink]

I don't know how to use Power Query (Get&Transform). Thanks for trying to help me.

 

[sandy666]

Select your range then use From Table (Data tab), it will open PowerQuery Editor, find Advanced Editor and paste there code copied from here
Make sure the name of the source table is the same as is in the code, here is: Table1

CITY	STATE	PHONE	gaaap	Numbers
Whiting	NJ	‭+1 (704) 806-3506		7048063506
Kendall Park	NJ	024 0608 8588		2406088588
Kendall Park	NJ	030 377 1554		303771554
Kendall Park	NJ	06-687 5888		66875888
Kendall Park	NJ	0757 7192169		7577192169
Lakewood	NJ	08503 810221		8503810221
Lakewood	NJ	0910 830 3999		9108303999
Lakewood	NJ	0917 535 9028		9175359028
Lakewood	NJ	0929 035 6104		9290356104
Whiting	NJ	095 935 6565		959356565
Whiting	NJ	1 512 473-2305		5124732305
Whiting	NJ	1 585-258-3696		5852583696
Whiting	NJ	1-(626) 539-0548		6265390548
Beaufort	SC	p:+1(602) 7054282		6027054282
Allendale	SC	p:+1(612) 750-9273		6127509273
Sumter	SC	p:+1(770) 362-2000		7703622000
Atlanta	GA	p:+1(979) 665-5052		9796655052
Atlanta	GA	p:+117579209003		7579209003
Atlanta	GA	p:+118014727251		8014727251
Salt Lake City	UT	954 793-1073		9547931073
Salt Lake City	UT	954 805-0608		9548050608
West Valley City	UT	954 805-6295		9548056295
Draper	UT	954 805-7107		9548057107
s.l.c.	UT	954 812-9849		9548129849
Salt Lake City	UT	954 815-4878		9548154878
Vernal	UT	954 815-5773		9548155773
Salt Lake City	UT	954 817-7959		9548177959
Salt Lake City	UT	954 821-3795		9548213795
Bountiful	UT	954 821-4484		9548214484

(this add-in doesn't work well, sometimes removing leading zeroes )
should be:

// ExtractPhoneNumber
let
    Raw = Table.TransformColumnTypes(Table.SelectColumns(Excel.CurrentWorkbook(){[Name="Table1"]}[Content],{"PHONE"}),{{"PHONE", type text}}),
    Text = Table.AddColumn(Raw, "Numbers", each Text.Combine(List.RemoveItems(Text.ToList([PHONE]),{" ","a".."z","-",".","+","(",")",",",":","("}))),
    Result = Table.SelectColumns(Table.TransformColumns(Text, {{"Numbers", each Text.End(_, 10), type text}}),{"Numbers"})
in
    Result

worth to read

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference

 

[tamwink]

I keep getting an error message.
This appears where I need to insert your code. Do I keep this text and add your code?

 

[tamwink]

Here's the error message:

 

[sandy666]

no, replace existing code with code from the last post then OK, then Close&Load

btw. why your pics are so big ?

 

[sandy666]

maybe with the live example will be easier
s666-PQ-PhoneNumbers