Format Phone Number to numbers only and according to country code

[GTBsha]

Hi everyone, got a file with a bunch of phone numbers how can I change the data into the following:
Automatically remove uneccesary details like parenthesis, spaces, dots or dashes and just convert it into numbers only
Add the country code
Avoid having duplicate country codes in the cell if phone number already contains a country code

 

[ExceLoki]

this should work in v2019

Book1
	A	B	C	D	E
1	country	country code	phone	country code	output
2	United States	US	7832237455	1	17832237455
3	Mexico	MX	52 124 361 70	52	5212436170
4	Canada	CA	1.613.284.5332	1	16132845332
5	Austaralia	AU	1 8186877577	61	6118186877577
6	Phillippines	PH	(985) 5258996	63	639855258996
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=CONCAT(IF(VALUE(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," ",""),"-",""),"(",""),")",""),".",""),LEN(D2)))=D2,"",D2),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," ",""),"-",""),"(",""),")",""),".",""))

 

[GTBsha]

This is great! Thank you!!

 

[ExceLoki]

happy to help