Excel Formula To Extract Country From Phone Number - Episode 2550
Microsoft Excel Tutorial: Is there an Excel formula that can return the country name from the telephone number?
This video shows you how. To make your life easier, add this worksheet with five named ranges already in it:
https://1drv.ms/x/s!As7G72Sl487Jlj1AU...
In this video, I take on a challenge from a LinkedIn user to extract the country name from a list of phone numbers using an Excel formula. After some initial attempts, I realized that the phone number length is not consistent and that I would have to account for all possible country codes. To do this, I found a website that listed all country codes and brought it into Excel. However, I encountered some unexpected duplicates, which I had to account for in my formula.
To organize the country codes by the number of digits, I created five named ranges for six-digit, four-digit, three-digit, two-digit, and one-digit country codes. Then, I used a VLOOKUP formula to extract the country code from the phone number. If the VLOOKUP returned an NA error, I used an IFNA formula to check the next set of digits until a match was found. I also provided alternative formulas for different scenarios, such as phone numbers without a plus sign or a mix of plus signs and numbers.
To make it easier for viewers to replicate this formula in their own workbooks, I have provided a link to a downloadable copy of the workbook used in this video. Simply copy the named ranges from the "CountryCodeLookup" sheet to your own workbook and use the formula provided to extract the country name from your list of phone numbers. I also included instructions on how to copy the worksheet to any workbook with a list of phone numbers.
I want to thank Daniel for tagging me in this challenge and for giving me the opportunity to share this solution with all of you. I hope this video helps you in your own Excel projects and I look forward to seeing you in the next netcast from MrExcel. Don't forget to like, comment, and subscribe for more Excel tips and tricks. Thanks for watching!
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
Table of Contents
(0:00) Get Country name from Telephone Number in Excel
(0:35) Can you simply use LEFT and LEN? No.
(1:14) Country data from web, but Duplicates!
(2:07) Canada by Area Code
(2:30) Sorting by Length of Country Code & Naming Ranges
(3:06) VLOOKUP of first 6 digits with IFNA
(3:45) The formula with 5 VLOOKUP
(4:48) Getting the 5 named ranges into your workbook
(6:35) Handling phones without leading + sign
(6:55) LET function to handle a mix of phone numbers
(7:13) Clicking Like really helps the algorithm
This video answers these common search terms:
excel formula to identify country from phone number
country code lookup in excel for phone numbers
determine country from phone number excel tutorial
excel formula to extract country from phone number
phone number country code detection in excel
identify country from international phone number excel
excel function for country lookup in phone numbers
how to find country from phone number in excel
excel vlookup for country from phone number
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Watch video Excel Formula To Extract Country From Phone Number - Episode 2550 online without registration, duration hours minute second in high quality. This video was added by user MrExcel.com 09 February 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 5,407 once and liked it 115 people.