While working on importing data from the internet or softwares like ACCESS, SQL, etc, there could be a need to update the text to proper case.
Excel provides built-in functions for users to change the case of the text to upper, proper & lower cases.
We will use the IF, UPPER, LOWER & EXACT functions together to make a formula that will work in a single cell taking a reference from the corresponding cell.
IF FUNCTION: The IF function checks if the condition you specify is TRUE or FALSE. If the function evaluates to true, it returns one value, else it returns the 2nd value given in the function.
Syntax = IF(logical_test,value_if_true,value_if_false)
logical_test: Logical test is the condition or criteria to be tested.
value_if_true: The value that should be returned if the logical_testreturns TRUE.
value_if_false: The value that should be returned if the logical_testreturns FALSE.
UPPER FUNCTION – The Upper function will convert a lower or proper case string into UPPER case letters. For example: abc or Abc to ABC.
text: Itis the text which should be converted to uppercase.
LOWER FUNCTION – The Lower function will convert UPPER or PROPER case string into LOWER case letters. For example, ABC or Abc to abc.
text: It is the text which should be converted to lowercase.
EXACT FUNCTION – The Exact function checks whether two text strings are exactly the same, and returns TRUE or FALSE. EXACT is case-sensitive.
text1: Itis the first text string to be compared.
text2: It is the second text string to be compared.
Let us take an example:
- We have some text in column A which is displayed in various cases – upper, proper, lower, etc.
- Enter the formula in cell B2
=IF(EXACT(A2,UPPER(A2)),”Upper Case”,IF(EXACT(A2,LOWER(A2)),”Lower Case”,”Upper and Lower Case”))
- The Exact function is used to determine whether the value returned is TRUE or FALSE.
- The value returned by the Exact function will then be passed on to the IF function.
- The IF function will treat this value as its first argument. If the value returns a TRUE then “Upper Case” will be the output. If the value returns a FALSE then another IF function will check if the value in the cell contains any lower case characters. If it’s found to be TRUE then “Lower Case” will be the output, but if its found to be FALSE then the output will be “Upper and Lower Case”
- By copying the formula to the below range, we will get the desired output.