Use UPPER LOWER Function in Excel - convert text from upper to lower case or from lower to upper case

Hi friends,

Today in this article I am going to show you two Microsoft Excel functions that are very useful if you need to convert texts from one case to another i.e., from upper to lower and vice versa. We'll discuss about these two functions and we shall use these function combined to get desired result by the help of examples. So, let us discuss all these three functions step by step, one after another.

How to use the UPPER Function

Description

The Microsoft Excel UPPER function allows you to convert all text to all uppercase.

The UPPER function is a built-in function in Excel that is categorized as a String/Text Function. The UPPER function can be entered as part of a formula in a cell of a worksheet.

Syntax

The syntax for the UPPER function in Microsoft Excel is:

=UPPER(text)

Parameters or Arguments

text

The string that you wish to convert to uppercase.

Example UPPER Function

Let's look at some Excel UPPER function examples and explore how to use the UPPER function as a worksheet function in Microsoft Excel:

Just type '=' sign and UPPER and then select the cell that you want to convert to upper case.

This function works fine in with numbers, spaces, special characters etc. Very simple function, the below picture will make it very clear.

How to use the LOWER Function

Description

The Microsoft Excel LOWER function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function.

The LOWER function is also a built-in function in Excel that is categorized as a String/Text Function. The LOWER function can be entered as part of a formula in a cell of a worksheet.

Syntax

The syntax for the LOWER function in Microsoft Excel is:

=LOWER(text)

Parameters or Arguments

text

The text/string to convert to lowercase.

Example LOWER Function

This function also works similar to UPPER function only the case is reversed, lower case. Let's look at Excel LOWER function example and explore how to use the LOWER function in Microsoft Excel:

Just type '=' sign and LOWER, hit tab in your keyboard and then select the cell that you want to convert to lower case.

This function also works fine with numbers, spaces, special characters etc. Very simple function, Lets' have a look at the below picture.

Now we'll try to change names in exact case from random upper or lower case, i.e., the characters in the name field could be in any case but  we'll capitalize the first character and the rest in lower case. So, lets' begin.

In the below example I've taken few names in first column and the next three columns are to convert it into upper, lower and in the last one to get the desired result, first character to be capitalized and the rest should be in lower case.

To get the output what I have done is first I converted the first character in capital letter using UPPER and LEFT function, then used LOWER and used LEN function to convert all the letters in lower case except the first one, and finally I combined these functions using '&' operator, you can use CONCATENATE function.

Lets break this example into step by step.
    1.    Use UPPER and LEFT function to convert first character into Capital Letter. Formula is =UPPER(LEFT(D3,1)) then
    2.    Get the remaining characters i.e., except first letter. Use RIGHT(D3,(LEN(D3)-1)), RIGHT function will take the characters from right side, LEN functions decides the number of characters in RIGHT function.
    3.    Cover it by LOWER function, formula now is            LOWER(RIGHT(D3,(LEN(D3)-1)))
    4.    Lastly, combine these two functions by & sign or use CONCATENATE function. The final formula is : =UPPER(LEFT(D3,1))&LOWER(RIGHT(D3,(LEN(D3)-1)))

Now, we have got one formula to capitalize the first character of a word, but what about a name space in between first and last name or middle name or one big paragraph and if we need to capitalize each and every first character of every word ?

Watch my next article on this problem. Hope you have enjoined a lot reading this article, thank you for reading...

Use TRIM Function in Excel to Remove Extra Spaces from Words Numbers Non printable Characters within a cell

In this article we shall discuss about the TRIM function in Microsoft Excel, sometimes working with data you might be noticed that some formula does not work although everything is right or proper . This is because some formula will not work in excel if there is extra space before a cell's data or after a cell's data (Leading or Trailing space) while trying to match columns or cells. Space(s) in excel is also a character which is not ignored by formulas. The problem with spaces are they are invisible to human eye, you need to take special care to detect them.

In this article we shall learn how to use TRIM function with some function combined or nested and will resolve or remove possible reasons of formula not working in Excel.

Description

Excel TRIM function returns a text value with the leading and trailing spaces removed. You can also use the TRIM function to remove unnecessary spaces between words in a string.

The TRIM function is a built-in function in Excel that is categorized as a String/Text Function.

Syntax

The syntax for the TRIM function in Microsoft Excel is:

=TRIM( text )

Parameters or Arguments

text

The text value to remove the leading and trailing spaces from.

Before going to several examples let us first look at nature of the problem with spaces.

Space Before or After a Data Creates Wrong Results

As you can see from the above image although both the texts are same in C and in D cell but when I ask excel to check whether these texts are equal or not, it replies me 'No' or 'False'. Human eye is showing these texts are same because we couldn't able to see the space before this text. Removing this extra space will resolve this problem. Or we can use TRIM function to remove the space.

Examples

Remove Spaces in Excel - Leading, Trailing, in between words

One more thing I would like to add here is that you can use LEN function to check the length of the text or contents in the cell and then you can count manually number of characters in that cell just to check it. 
Let me show you the working of TRIM function by the help of the below image.


To use TRIM function you need to type an '=' sign and then TRIM, press tab and move your cursor to the cell to trim. In the below picture I am showing you how to trim a word, spaces within words or spacing between words, spaces before numbers, spaces after numbers and both.

Use of TRIM Function in Excel

Please note the difference in number of of characters in the B column and in the E column, before and after use of TRIM function. It is very easy function but it can save your time of troubleshooting a problem in formula. Remember a trailing or leading spaces in any cell will make your formula inconsistent.

Remove Non printable characters

When you import data from external sources, it's not only extra spaces that come along, but also various non-printing characters like carriage return, line feed, vertical or horizontal tab, etc.

The TRIM function perfectly removes white spaces, but it cannot eliminate non-printing characters. Technically, Excel TRIM is designed to only delete value 32 in the 7-bit ASCII system, which is the space character.

To remove spaces and non-printing characters in Excel, use TRIM in combination with the CLEAN function. As its names suggests, CLEAN is purposed for cleaning data, and it can delete any and all of the first 32 non-printing characters in the in the 7-bit ASCII set (values 0 through 31) including line break (value 10).

Syntax of CLEAN

=CLEAN(text)

It removes all the non printable characters in a cell. Let us have an example. In the below example I have typed few non printable characters. This type of or other non printable characters you may get while importing data from other sources. In this situation what I have done is that I used CLEAN function to remove these non printable characters and cover it by TRIM function to remove the extra trailing of leading spaces. Please follow the below image to understand this small but useful function.

How to use of TRIM and CLEAN Function in Excel

Please note that yellow colored non printable characters, remove it by CLEAN and TRIM function. For the above example the formula is =TRIM(CLEAN(B3)).

I hope you liked this article, thank you for reading....

MID function - The Replacement of Nested LEN LEFT and RIGHT function step by step

Hi Friends,

In my last article I discussed about the function LEN() LEFT() and RIGHT() step by step by the help of  two basic Excel examples. In this article I'll show you another new function MID() and shall show you how beautifully this function can be used to get the data from the middle of a cell. 

This will replace the use of nested LEFT and RIGHT function.

Description

The Microsoft Excel MID function extracts a substring from a string , starting at any position and ending at given position.

The MID function is a built-in function in Excel that is categorized as a String/Text Function.

Syntax

The syntax for the MID function in Microsoft Excel is simple, consists of three parts :

MID( text, start_num, num_chars )

Parameters or Arguments

text : The string or text in a cell that you wish to extract from.

num_chars : The position in the string that you will start extracting from. The first position in the string is 1. Remember it is not Zero.

num_chars : The number of characters that you wish to extract.

For Example : If E1481210045089M52 is my given cell content and suppose I need data starting from third position to seventh position then this number would be 4812100.

The use of MID function in excel is very simple for the above example it is =MID(B2,3,7) as shown in the below image.

You type '=' sign type MID and press tab to get first bracket then select the cell from which you want your data and put comma type the starting number from which you want to count and then again put a comma and give the ending number. This will give you data from the middle of a cell's data.

How to use MID Function in Excel

We can use LEFT and RIGHT (with LEN for more dynamic  and also without LEN function) functions in nested conditions. This requires a simple mathematics and you need to count the characters and put in the formula. So, lets' do it.

First we need to use LEFT function to get the data upto 9th position, then we have to use the RIGHT function starting from right side upto 7th position. The formula now becomes =RIGHT(LEFT(B2,9),7)

But the benefit of MID function is that it can easily replace the use of nested LEFT and RIGHT function in some cases. MID function allows us to select specific data from a cell by providing starting number and ending number.

How to use MID LEN LEFT and RIGHT Function in Excel

Example : Use of LEN() with LEFT() function and use of LEN function with RIGHT function.

In this example I shall show you how to use LEN with LEFT and LEN with RIGHT function. As you can see from the below image I have two tables two columns, one column is common Product Code. If you look carefully then you can notice that every product code is either ending by '- and text' or 'text and -', we need to get the data but without '-text' or 'text-', we shall use LEN with LEFT and LEN with RIGHT to get the desired data.

Use of LEFT() and LEN() : My data in A2 cell is E148121009M52-TEST, I need to remove -TEST  and print this in B2 cell, lets' do it.

We all know that Syntax of LEFT is =LEFT(text,[num_chars]),  so write =LEFT and hit TAB key in your keyboard which will create an opening bracket and then select A2 i.e., text parameter. Now for the second parameter [num_chars] we'll use LEN function, write LEN(A2), and as we can count that the text after the characters E148121009M52 is of five characters so we need to deduct five from the total length of the string in A2.

Our formula now becomes, =LEFT(A2,LEN(A2)-5) and this will return the desired data without '-TEST'

Use of LEFT() and LEN() : The process is same as the previous example, the only difference is that the text in D2 i.e., TEST-E148121009M52 has extra characters 'TEST-' in the beginning of the data in D2. If you write this formula it will be like =RIGHT(D2,LEN(D2)-5)  and this will remove the five characters in the beginning of this text.

LEN with LEFT and RIGHT Function in Excel

You need to know how to use LEFT and RIGHT function works because sometime MID will not be applicable in some situations, the option will be to use either LEFT or RIGHT function with or without LEN function.

Thank you for reading...

LEN LEFT and RIGHT function in Excel with Basic Excel Examples

Hi Friends,

In this article I shall discuss about few small, easy but useful Excel functions. Proper combination of these function can solve a larger problem within seconds. Today I'll discuss about three functions LEN(), LEFT() and RIGHT() step by step by the help of  two basic Excel examples. And in my next article I'll show you how easily and quickly we can use these function to break a complicated data into smaller pieces, but before that lets' understand these functions first.

Description

The Microsoft Excel LEN function returns the length of a specified string, LEN meaning LENGTH. The LEN function is a built-in function in Excel that is categorized as a String/Text Function.

Syntax

=LEN(text)

Very simple syntax !!! Isn't it ?

The working of this function is like you enter a text within this function and it will tell you the length of the text. 

But what if I enter a combination of numbers like 1132487 or number and text which we call alphanumeric like FED10093452d17HH.

Remember space is also treated as a character, therefore, while counting the length of a string by LEN function you have to count it.

This LEN function will count how many characters' are in the supplied string. Very simple to use, just type equal to sign (=) and type three characters LEN, put a tab, it will show a opening bracket. Now, select the cell for which you want to get the number count of characters or numbers or both or alternatively you can type the text within this function but don't forget to cover it by double quotes.

So, lets' take an example.  As you can see from the below images that I have four column headers Product Code 1, Product Code, LEN function Example 1 and LEN function example 2.

I need two data in last two columns the count of characters in column 'A' & column 'B' in column 'C' & 'D' respectively.

How to use LEN function in Excel

How to use LEN function in Excel to measure Length of  cell content

I hope you have understand this function very well. So lets move to the next two functions LEFT and RIGHT.

In this section I'll discuss about two functions together one after another and step by step as these functions are very similar in nature the difference is one and I'll show this difference in this article.

LEFT() and RIGHT() functions are also categorized as a String/Text Function.

Syntax

=LEFT(text, number of characters) and =RIGHT(text, number of characters)

I am discussing these function in one time because these functions are very similar as you can see from the syntax of these functions only difference is that they work oppositely in direction.

Parameter

text - input any text (you can enter number also it will work)

number of characters : this is the number that you want to get from the input text.

Let us have an example to make it more clearer.

=LEFT(text, number of characters) : As the name of this function indicates it is related to LEFT side of a text string and actually it gives you that number of characters from a string from left side which you instruct this function to show.  What it does is that if you enter a string as first parameter in LEFT function and number of characters you need to cut from left side of that text  input as second parameter then this function will give you out of that number of character as you mentioned in the number of characters parameter.

The below example will make it clear.

The working of the RIGHT() function is same, the only difference is that it will give you the output from the right side of the text.

One more thing, you can also enter the text input as

=LEFT("ADVANCED",3) or =RIGHT("ADVANCED",3)

The output of these formula would be 'ADV' for LEFT function and 'CED' for RIGHT function. Because the three character from lefet side of the text "ADVANCED" is 'ADV' and similarly from right side it becomes 'CED'.

So lets' see the below examples.

How to use LEFT Function in Excel

How to use RIGHT Function in Excel

Now what if I need to get the data from the middle of a string for example, if I have been given E1481210045089M52 as my input data and is asked to get the figures after 4 character upto 5 character (the red colored characters). Then what to do ?

Simple, I need to make a nested formula using LEFT and RIGHT function. But here is a trick, let us analyze the question first.

I need 5 characters from the 5th character of the given string this means that there are two steps, one is I need to get the 9 (nine) characters from the left side i.e., upto E14812100 and then I need to extract five characters from right side i.e., 12100 and my problem will be resolved.

If I write these steps in the form of formula the this will look like : =LEFT("E1481210045089M52",9) for step one and the output would be E14812100. And now if I cover it by RIGHT function then it will look like =RIGHT(LEFT("E1481210045089M52",9),5) and the result will be 12100. 

Please follow the image below, it will clear this thing easily and quickly. For ease I have shown three steps first is LEFT function, second is RIGHT function and the last one is nested LEFT and RIGHT function.

LEFT and RIGHT function combined

Hope you have enjoyed this article. In my next article I will show you few more examples on these three functions i.e., LEN, LEFT & RIGHT in more detail.

Thank you ... Stay Blessed....

IF with AND function in one formula with example step by step - Basic Nested IF Excel IF SUM and AND function

Hi Friends,

As I've promised in my last article, today I'll show you in detail the nested IF functions with IF() function and with AND function to resolve a complicated problem by the easiest methods or tricks.

Today I'll prepare a mark sheet with few conditions given below to understand the nested IF() and AND() function combined. We'll use Nested IFs with AND() Function.

Conditions

      1.      Every student should have to get minimum of 45 marks in each subject.

      2.      Total marks should be greater than 120

      3.      Grade :

a)      "A" - Greater than 80%

b)      "B" - Greater than 60%

c)      "C" - Greater than 50%

d)      "No Grade" - For Failed Students.

Understanding IF Function in Microsoft Excel

As the image shows I have written four column headers like Total Marks, PASS/FAIL, % and Grade to make things easy to understand. Now, I'll describe each column header step by step.

Total Marks

In the Total Marks column I just used SUM() function to get the total number for each student. So, that's the easy part.

PASS or FAIL

To judge whether a student has "pass"ed all the subject we'll use nested IF function (IF within IF) and 'll create a nested formula using AND function. Because IF function can normally can take only one logical test as input.

Now to appear in each subject every student should have to get 45 marks in each subject and if one of these three subject's marks is below 45 then the student will not Pass.

The syntax of IF function is =IF(logical_test, value_if_true, value_if_false), therefore, we can write our IF formula in G3 cell (refer the above image) for this example as =IF(D3>45 and ... but what about next two subjects (?) cell address is E3 & F3. Now its time to use AND() function, as AND function can take upto 255 input as its parameter and the most important characteristic of this function is that if any one of the given conditions within AND function returns FALSE, it will return FALSE logical output meaning that if we write "FAIL" in the third parameter i.e., value_if_false of IF function then FAIL will be printed whenever the output of AND function is FALSE and if we write "PASS" in the second parameter of IF function i.e., value_if_true part and if the output of AND function is true then "PASS" will be printed. Easy??? Yes it is. So Lets' do it.

Now if I individually write AND function for this problem it will be like this... =AND(D3>=45,E3>=45,F3>=45).

If we press enter it will show us either TRUE (if marks of all the three subject is greater than or equal to 45) and FALSE (if marks of all the three subject is less than 45).

Now we'll just cover this function by IF function and additionally write "PASS" in the value_if_true part and "FAIL" in the value_if_false part like ...

 AND function => =AND(D3>=45,E3>=45,F3>=45)

Cover by IF  => =IF(AND(D3>=45,E3>=45,F3>=45),"PASS","FAIL")

How IF function Works

"One thing I would like to share to you is that you can remember IF function like this "IF this satisfied then THIS else THAT" and remember that THEN & ELSE is a comma (replace by comma). This, I learnt from my first computer teacher."

Now press enter and you are done, your formula will print PASS or FAIL depending upon the marks obtained.

IF AND function combination

Now you might be little surprised to see the G3>120 additionally in the AND formula, its nothing but the second condition that Total Marks should be greater than 120.

You just put a comma at the end of the AND function and type or select G3 and tell IF() that it should be greater than 120, that's' it, done.

 =AND(D3>45,E3>45,F3>45,G3>120)

If you drag this formula to the end of the data, it'll show you the Pass or Fail of each student.

% Percentage

I did it here very simply by dividing Total marks by 300 as 100 marks is the highest marks for each subject.

GRADE

Here we'll use nested IF function but before that lets understand the problem as understanding the problem will make our task easier.

The task has two parts, one based on the percentage achieved in three subject we have to put Grade and number two if any student is FAILED then there will be printed No Grade.

If we think logically then first of all we need to check the second condition because if any student has failed then no further checking is needed directly we can print "No Grade!!!".

So we'll begin with =IF(H3="FAIL", "No Grade", ...) here H3 is the cell GRADE for first student. Now if the student has no FAIL status then the IF function will move to value_if_false part and here is the trick, we'll again insert another IF function like =IF(H3="FAIL", "No Grade", IF()) and within this second IF function we'll write (I3>=80%,"A",) and like the above method we'll insert another IF function in the  value_if_false part of this new inserted IF() and after that another and close this formula by braces (opening and closing braces or brackets should be equal otherwise Excel will give you error) finally our formula will look like

=IF(H3="FAIL","No Grade",IF(I3>=80%,"A",IF(I3>=60%,"B",IF(I3>=50%,"C"))))

How to use IF with AND function in combination

This is how to use Nested IF and Nested function AND(). Below image will make you very clear about the topic discussed.

Nested IF function in Excel

You can watch video clicking in this here or below video frame.

I hope this part will make your understanding of IF function more clearer, if you like this blog please click on the follow button and keep commenting.

Thank you for reading...