topic Re: Match a phone number to a list of country codes in Desktop

Match a phone number to a list of country codes

Nick — Thu, 25 Aug 2016 21:37:21 GMT

I am trying to match a phone number to a list of country codes in excel to know the country.

The problem is that I want to match the first x characters of the phone number to the country code (where x is the length of the country code in the list) as not all the country codes have the same length (eg. US:1 UK: 44 Jordan:962, UAE:971, Bahrain:973).

I was able to do it in Excel via the following formula, but now struggling to convert it to Power BI/Query:

=LOOKUP(0,-SEARCH(LEFT(A1,LEN(CountryCodes!$D$1:$D$6))+0,CountryCodes!$D$1:$D$6),CountryCodes!$E$1:$E$6)

where CountryCodes!D1:D6 - Country Code
CountryCodes!E1:E6 - Country

Is this feasible?

Thanks in advance,

Nick

Re: Match a phone number to a list of country codes

BhaveshPatel — Thu, 25 Aug 2016 21:42:20 GMT

SAMPLE DATA PLEASE SIR

Re: Match a phone number to a list of country codes

Nick — Thu, 25 Aug 2016 21:56:51 GMT

Is this enough?

Re: Match a phone number to a list of country codes

BhaveshPatel — Thu, 25 Aug 2016 22:17:57 GMT

THIS WOULD BE OF GREAT HELP.

https://www.powerquery.training/portfolio/replicate-excels-vlookup-function/

Re: Match a phone number to a list of country codes

Nick — Thu, 25 Aug 2016 22:59:37 GMT

Thanks.

I tried already yesterday but could not get it work so far since VLOOKUP approwimative match doesn't help really.

Here is a false result for anumber from North America:

Regards,

Nick

Re: Match a phone number to a list of country codes

BhaveshPatel — Fri, 26 Aug 2016 13:38:56 GMT

Hi Nick

Below is the the link to the country codes.

Create a conditional column in the powerquery based on the below list.

https://drive.google.com/file/d/0B5-C_3XrFPdOZXkwNkZXb3pGdGs/view?usp=sharing

This would solve your problem.

This is a really lenghty solution but it definitely works.

Regards,

Bhavesh

Re: Match a phone number to a list of country codes

Nick — Sun, 28 Aug 2016 21:21:46 GMT

Great thanks! Had to fix some syntax error (lowercase and speeling syntax) but all good. Thanks again, Nick

Re: Match a phone number to a list of country codes

grprem81us — Tue, 18 Sep 2018 12:28:51 GMT

This does not work in Power BI Query.

Can anyone help to convert this to Power Query

=LOOKUP(0,-SEARCH(LEFT(A1,LEN(CountryCodes!$D$1:$D$6))+0,CountryCodes!$D$1:$D$6),CountryCodes!$E$1:$E$6)

Thanks in Advance.....

Re: Match a phone number to a list of country codes

piun — Wed, 06 Dec 2023 17:09:42 GMT

can you send the link ?