Here, we will learn how to use the Excel logical function: SWITCH. Later, we will also learn how to extract country from phone number using SWITCH with the LEFT function, that extracts the characters from the left side of the string.
SWITCH function
The Excel SWITCH function compares the value against the list of values and returns the result corresponding to the first match found.
SWITCH takes four primary arguments: expression, value1, result1 and default. The first required argument expression, will take the value to match against. User can enter the value directly in the formula, or a cell reference containing the value, or the formula that returns the value to match against. The second required argument value1 is the value compared against expression. The third required argument result1 is the value to be returned when corresponding value1 matches the expression. User can enter up to 126 pairs of value and result arguments. The fourth optional argument default, will take the value to return when no match is found.
Extracting country from phone number using SWITCH with LEFT
To extract the country from phone number, use the SWITCH formula and nest the LEFT function in the expression argument. My goal is to extract the first two characters from each string in column A using LEFT. The result value will be matched against the list of values in the value arguments and when it matches, the corresponding value in the result argument will be returned as the final output. The formula in B3, copied down, is:
=SWITCH(LEFT(A3,2),"53","Cuba","86","China","51","Peru","91","India","?")
How this formula works
LEFT extracts the 2 characters from the left side of the string in A3, that is "53". This value is compared against the values in the value arguments. The value "53" matches the value in the value1 argument. Therefore, the formula returns the corresponding result1 value "Cuba" as the final output. When there is no match, the formula will return the value "?" in the default argument, like in B12 and B18.
You have successfully learnt how to use the Excel logical function: SWITCH, and how to extract the country from phone number! I hope this post helped you.
