PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
Think of a report where we are putting grades against a value say Marks of the students.
Although the grades can be in the same case but recently we saw this situation where the grades were in the upper or lower case.
Another situation can occur when we have to find the case of a letter at a particular position in the Text.
In this article, we would focus on both the case.
1. How to check if the Letter present in the cell is having upper case or lower case, and
2. How to check the case of the letter present at any position in the Text.
The concept is very necessary for the innovation to take place.
So, let us understand the concept behind the solution to this problem.
Any character whether its alphabet, big or small, symbols etc. are encoded by the ANSI. It means that all the characters are given the codes which is a number from 0 to 255.
For example, A has a code of 65 and Z has a code of 90.
Similarly a has the code of 97 and z has the code of 122.
Excel has one dedicated function to find out the CODE OF THE CHARACTERS which is known as CODE FUNCTION.
So , the trick is , if we compare the CODE of the given character with the range provided, we can surely know about the case of the Character and proceed further.
Let us take an Example where we have different cells containing the single letter representing the PRIMARY and SECONDARY status by the upper and lower case.
Suppose the capital letter that is upper case shows the primary status and small letter shows the secondary status.
The character shows the group and can be a,b,c,d or e.
CONDITIONS:
If the letter is capital , the status is PRIMARY, if the letter is small , the status is SECONDARY and if any wrong input is there, the status is CHECK THE GRADE.
EXAMPLES:
EXAMPLE | GRADE | EMP ID |
1 | A | 101 |
2 | b | 113 |
3 | B | 121 |
4 | D | 155 |
5 | d | 205 |
6 | a | 698 |
7 | c | 789 |
8 | C | 988 |
STEPS:
Let us try to understand the formula used.
The formula used is =IF(AND(CODE(G7)>64,CODE(G7)<91),”PRIMARY”,IF(AND(CODE(G7)>96,CODE(G7)<123),”SECONDARY”,” CHECK THE GRADE”))
The general construction of the formula is NESTED IF.
Extending the problem discussed above a bit further, let us find out if a character on certain position is capital or small.
We have already learnt the trick of checking the case of the alphabet.
We’ll solve this problem by joining our CODE FUNCTION with MID FUNCTION.
Let us take a few examples and find out the case of the character at the mentioned positions.
EXAMPLE | TEXT | POSITION TO BE CHECKED |
1 | HELLO | 3 |
2 | How | 2 |
3 | Are | 1 |
4 | yoU | 2 |
5 | welcome | 6 |
6 | To | 1 |
7 | gyanKOSH | 3 |
8 | .net | 1 |
CONDITIONS: The text contains capital or small letters.
STEPS:
Let us try to understand the formula used.
The formula used is =IF(AND(CODE(MID(G20,H20,1))>64,CODE(MID(G20,H20,1))<91),”CAPITAL”,”SMALL”)
We start with the IF FUNCTION .
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE