INTRODUCTION
Whenever we prepare any report in excel, we have two constituents in any report.
The Text portion and the Numerical portion.
But just storing the text and numbers doesn’t make the super reports. Many times we need to automate the process in the reports to minimize the effort and improve the accuracy.
Many functions are provided by the Excel which work on Text and give us the useful output as well. But few problems are still left on which we need to apply some tricks with the available tools.
In this articles , we are going to learn many tricks and usage of function to perform different operations on the TEXT IN EXCEL.
HOW TEXT IS HANDLED IN EXCEL?
TEXT is simply the group of characters and strings of characters which convey the information about the different data and numbers in Excel. Every character is connected with a code [ANSI].
Text comprises of the individual entity character which is the smallest bit which would be found in Excel.We can perform the operations on the strings[Text] or the characters.
Characters are not limited to A to Z or a to z but many symbols are also included in this which we would see in the later part of the article.
TEXT IS AN INACTIVE NUMBER TYPE[FORMAT] IN EXCEL. ANYTHING STORED AS TEXT [NUMBER OR DATE] WON’T RESPOND TO ANY STANDARD FORMULAS OR FUNCTIONS BUT SPECIALLY DESIGNED TEXT FUNCTIONS. [EXCEPTIONS DO OCCUR IN CASE OF NUMBERS]
If we need to make anything inactive, such as Date to be non responding to the calculation, we put it as a text. Similarly if we want to avoid any calculations for a number it needs to be put as a text.
COMPARE TWO TEXT STRINGS IN EXCEL
This situation can occur when we want to create some logical condition and check whether the two text strings are same or not.
Now the same can have two meanings.
1.Just the content is same. For example “TEXT” and “text”.
2. Exactly same [ Content is same and case is same]. For example, “teXT” and “teXT”
COMPARING THE TEXT FOR THE CONTENT:
- We are going to compare just the content and not the case.
- This can be used simply in Excel.
- We can simply compare the cells by using the formula =Cell 1 containing text=cell 2 containing text.
- This formula will return TRUE if the content is same [ignoring the case] and false if content is not the same.
COMPARING THE TEXT FOR THE EXACT CONTENT [CONTENT AND CASE]:
- We are going to compare the two text strings exactly for the content and the case.
- This is also not difficult.
- We will make use of the function called EXACT(text 1, text 2). It compares the text to be exactly same and returns true if the texts have same content and same case. [EXACT TAKES ONLY TWO TEXT AT A TIME].
- We can simply compare the cells by using the formula =EXACT(Cell 1 containing text,cell 2 containing text]
- The formula will return TRUE if both the text strings are same otherwsie FALSE.
COMBINE TEXT IN EXCEL
Combining text is excel is very useful technique.
Let us understand what we mean by combining the text.
The combining means to stick the text to one another. For example if one cell is having “ABCD” and the other is having the value as “EFGH”. If we combine them in the third cell the result will be “ABCDEFGH”.
The specific term used for sticking the text or combining the text or joining the text in the computer language is CONCATENATE which simply means to stick or append.
We can join the text in Excel in many ways.
- USING & OPERATOR
- USING CONCATENATE OR CONCAT FUNCTION
STEPS TO USE ” &” [CONCATENATION] OPERATOR TO JOIN TEXT IN EXCEL:Just like the other operators in Excel such as + – / * : etc. we have “&” which is known as CONCATENATION OPERATOR.We can make use of this operator directly to join any text in any number of cells.
For Example Suppose we have a text ” AB” in cell A1 and “CD” in cell A2.Let us join them and get the result in the cell A3 then we have to put the formula in A3 as=A1 & A2It’ll give us the result in A3 as ABCD.
STEPS TO USE CONCATENATE OR CONCAT FUNCTION:Just like the & operator , we have got two functions dedicated for this particular job of combining the text in Excel.The functions are CONCAT or CONCATENATE.CONCAT is just the advanced version of CONCATENATE FUNCTION, so we will cover both of them simultaneously.Follow the procedure.
- Select the cell where we want the result.
- Put the function as
- =Concatenate(cell 1, cell2, cell3,….) or =Concatenate(value1, value 2, value 3,…) .[ text value must be in “” to specify that it is text]
- The same can be done with the CONCAT FUNCTION.[This function is available in EXCEL 2019 onwards]
- Put the function as
- =Concat(cell 1, cell2, cell3,….) or =Concat(value1, value 2, value 3,…) .[ text value must be in “” to specify that it is text]
The difference between CONCAT FUNCTION and CONCATENATE FUNCTION is that CONCAT function can accept a complete Range of cells to act upon for example If we want to concatenate the cells from A1 to A5 we can simply write CONCAT(A1:A5).EXAMPLE:
- We have the text A B C D E in the cells A1 B1 C1 D1 and E1 respectively.
- We would use CONCAT and CONCATENATE FUNCTION to combine these.
- We choose the cell D4 and D5 for the output.
- In D4 we put the formula as =CONCATENATE(A1,B1,C1,D1,E1)
- The output is shown as ABCDE which was expected. We can use the CONCAT FORMULA for the same result.
- In D5 we use the formula as =CONCAT(A1:E1) and the result is ABCDE as expected.
SEPARATE/SPLIT TEXT IN EXCEL
SPLITTING/SEPARATING the text is also required many times in Excel.
This can be stated as removing any portion of the text or string from the cell and place it into the different cells.
Splitting and separating the text can be done in a many ways. For example
- TEXT TO COLUMN– We can split the text in a cell into the different columns. The basis of the separation can be any delimiter like a comma, colon etc.
- USING FLASHFILL
- Separating a single word into many portions.
- Separating Text on the basis of any character or pattern
TEXT TO COLUMN:
We’ll discuss it here briefly as a complete detailed topic is already present. CLICK HERE TO CHECK OUT THE COMPLETE ARTICLE ON TEXT TO COLUMN. Text to column is used in a situation when we need to split any string [sentence of few words] separated by any delimiter [ comma, colon , full stop, semicolon, space etc. The process is backed up by a wizard mechanism which makes it very simple. The only requirement is that it needs a delimiter, some character on the basis of which it’ll separate the string into words.
USING FLASHFILL:
FLASH FILL is a new tool for many kind of task which would otherwise take much time. For such tricky tasks such as separating the words or auto filling or many such kind of tasks which has some pattern are well suited for the FLASH FILL. It is a complete topic discussed in detail. CLICK HERE TO CHECK OUT THE COMPLETE ARTICLE ON FLASH FILL IN EXCEL.
SPLIT TEXT INTO MANY FRAGMENTS:
In this section we will learn to separate any text into many portions even if there is no delimiter. [Of course if we’d be having delimiter we’ll be using Text to Column option].
We perform this action with the use of LEFT, RIGHT AND MID FUNCTIONS.
LEFT FUNCTION has the capability to pick the specified number of characters from the left side of the text.
RIGHT FUNCTION can extract the specified number of characters from the right whereas
MID FUNCTION takes out the specified number of the characters from the middle of the text.
Let us find out the way we can use these functions to extract the values from the text.
EXAMPLE 1: SEPARATING THE DATE IN EXCEL
This problem occurs mostly when we have imported the data from somewhere and the date is in some format which doesn’t suit our system. The date can also be without any delimiter just like
11032011
12032011
13032011
and so on.
SOLUTION:
In this problem , first of all we have to check the pattern. We can see that the left two digits are the dates, after that the month and then the year.
If we want to put the date, month and year in the separate columns, put the following functions in the column for
DATE, MONTH AND YEAR.
FORMULA FOR DATE COLUMN =LEFT(CELL CONTAINING THE DATE,2) [PICKING 2 LETTERS FROM THE LEFT]
FORMULA FOR MONTH COLUMN =MID(CELL CONTAINING THE DATE,3,2) [STARTING FROM THE LETTER POSITION 3, PICKING 2 LETTERS]
FORMULA FOR YEAR COLUMN =RIGHT(CELL CONTAINING THE DATE,4) [PICKING 4 LETTERS FROM THE RIGHT]
The result will be the date, month and year separated and in three columns.
EXAMPLE 2: SPLITTING A RANDOM TEXT IN PARTS
To further understand the usage of functions LEFT, RIGHT and MID, let us take one more example and check how we can use them.
Suppose we have the WEEKDAYS LIST and we need to limit the letters upto 4 only.
We can simply use the LEFT FUNCTION.
To Extract the 4 letters from the left of the weekdays we can use the formula as =LEFT(CELL CONTAINING WEEKDAY,4). It’ll extract the letters from the left as shown in the picture below.
If we look at the results, we can see that the function did what we wanted but as per standards , in a week we sometimes take 4 letters and sometimes 3 letters from the left. Let us refine the function and make it proper.
We know that the 4 letters are taken for TUESDAY ONLY. We can put an IF function in a way as
=IF(L4=”TUESDAY”,LEFT(L4,4),LEFT(L4,3))
If the day is TUESDAY , 4 letters would be taken from the left otherwise only 3 will be taken from the left.
The result is shown in the picture below which is better than the previous one.
THERE ARE MANY STANDARDS OF THE SHORT FORMS OF THE WEEKDAYS. THE MOTIVE HERE IS TO UNDERSTAND THE USAGE OF THE FUNCTION.
Similarly we can split any text if we know the position of the split.
Let us find out the way to split the text if we don’t know the position exactly.
SPLITTING/SEPARATING TEXT ON THE BASIS OF PATTERN OR PRESENCE OF CHARACTER:
Let us not check out the most important part of the search and split trick in Excel.In this process we need to search for a character and then act according to the situation.We would learn this process using the examples.
SPLITTING FIRST NAME AND LAST NAME IN EXCEL:
Suppose we have a column with the first and last name put in the same cell.
If we want to apply any kind of formula on this, we need to separate or split the first and last name.
Suppose the data given is
Kim Rogers
Daniel Bros
Allison Berger
Joe Wilson
Ravi Kumar
Ashfaq Akhtar
We can do that using the following methods.
1. TEXT TO COLUMN– We can use this trick confidently and easily to separate the first and last name within seconds. [CLICK HERE TO LEARN TEXT TO COLUMN WITH PICTURES AND EXAMPLES].
After applying the text to column, we will be having the following data.
2. USING FLASH FILL TO SPLIT NAMES OR TEXT:
As already discussed we can use FLASH FILL also to separate the names into first name and last name in Excel.
[CLICK HERE TO LEARN FLASH FILL IN EXCEL]
STEPS TO USE FLASH FILL:
Attempt one sample manually. For our example Split the first row which is Kim Rogers as Kim and Rogers in the other column as shown in the picture.
- Select Next cell to continue the process.
- Click DATA>FLASH FILL.
- If EXCEL has understood the pattern , the next name i.e. Daniel will be filled here. The complete column will be filled automatically.
- Similarly select LAST NAME for DANIEL BROS and click FLASH FILL. The complete column will be filled.
- We are done.
3. USING FUNCTIONS TO SPLIT NAMES OR ANY TEXT INTO PARTS
After the automated options let us try now the manual one, of course using the functions.
Here are our requirements.
A function which can
- Search any character [We can use SEARCH function or FIND for the same.
- Functions which can extract characters from the TEXT. [LEFT, MID AND RIGHT FUNCTION are the best contenders for this task].
- Function which can return the length of the text. [LEN FUNCTION is suitable for this task].
With the use of these function , let us try to separate the text in Excel.
EXAMPLE 1: SPLITTING THE NAMES USING FUNCTIONS IN EXCEL:
Let us try our same example first.
STEPS TO SPLIT THE NAMES WHICH CONTAIN SPACE AS THE SEPARATOR
- Select the cell where we want the first name.
- Enter the formula as =LEFT(D17,SEARCH(” “,D17,1)-1) where D17 is the cell containing the NAME. [In this step we use the LEFT function to extract the characters from the left. We take the first input as the cell containing the complete name,. The second input is taken with the help of Search function which will compare space ” ” in the complete name from the character 1 and return the position of the space, which will become the length of the characters from the left.
- Enter the formula =RIGHT(D17,LEN(D17)-SEARCH(” “,D17,1)) [This function works on the same principle as the previous step. The function is RIGHT and takes the first argument as the complete name which is in D17 for our example. The LEN(D17) returns the complete length of the string. We subtract the location of the SPACE ” ” using the same method which is using the SEARCH FUNCTION. This combination gives us the location of the starting of the second word.
- Drag both the formulas down the column.
- The result is shown in the pic.
EXAMPLE 2: SPLITTING THE NAME/TEXT OF MORE THAN TWO WORDS IN EXCEL
We just learnt how to separate the Names in two different cells. Now let us learn to separate the names which contain a Middle name into Three different portions.
STEPS TO SPLIT THE NAMES WHICH CONTAIN MIDDLE NAME TOO.
- Select the cell where we want the first name.
- Enter the formula as =LEFT(H4,SEARCH(” “,H4,1)-1) where H4 is the cell containing the NAME. [In this step we use the LEFT function to extract the characters from the left. We take the first input as the cell containing the complete name,. The second input is taken with the help of Search function which will compare space ” ” in the complete name from the character 1 and return the position of the space, which will become the length of the characters from the left.
- For the middle name we need to use the MID FUNCTION. Select the cell for the MIDDLE NAME and put the following formula =MID(H4,SEARCH(” “,H4,1)+1,SEARCH(” “,H4,SEARCH(” “,H4,1)+1)-SEARCH(” “,H4,1)) . [The function starts with MID as the top function. The first argument is the H4 which is the cell containing the complete name. The second argument is the starting character sequence number. We used SEARCH FUNCTION and try to search ” ” in H4. We add one to this as we need the first letter of the middle name. The third argument is the number of letters to be taken for the middle name. We found it out using the SEARCH FUNCTION again but started the SEARCH FROM THE NEXT SPACE” ” which would return the space after the middle name or second word. Subtract this position from the position of the first space. Now we have the number of letter we want.
- The LAST NAME or the third word can be found by using the following formula. Enter the formula in the LAST NAME COLUMN AS =RIGHT(H4,LEN(H4)-(SEARCH(” “,H4,SEARCH(” “,H4,1)+1))) [This function works on the same principle as the previous step. The function is RIGHT and takes the first argument as the complete name which is in D17 for our example. The LEN(D17) returns the complete length of the string. We subtract the location of the SPACE ” ” using the same method which is using the SEARCH FUNCTION. This combination gives us the location of the starting of the second word.
- Drag both the formulas down the column.
- The result is shown in the pic.
IF STATEMENT WITH TEXT
IF function is one of the most important function in Excel.
Life is full of conditions and decisions. The same option in Excel is fulfilled by the IF FUNCTION.
In this section, let us try to use IF with the TEXT and try to solve many problems.
The learning is best using the examples, so we will learn by discussing the examples.
EXAMPLE 1: USING IF FOR TEXT AND PERFORM FURTHER ACTION
Let us start with the simple example.
Suppose we have two columns. One column is having the values as YES and NO.
We will put the value PASS if the first column contains YES otherwise NO.
DECIDING FACTOR | DECISION |
YES | |
NO | |
YES | |
YES | |
NO | |
NO |
We have to take the decision using the IF and checking the presence of TEXT in the first column.
STEPS TO USE IF FOR TEXT AND TAKE DECISION:
- Select the cell in the first decision cell and write the function as =IF(DECIDING FACTOR CELL=”YES”,”PASS”,”FAIL”).
- For our example, we have the deciding factor in E7 so our formula becomes IF(E7=”YES”,”PASS”,”FAIL”)
- The result came out as PASS which is correct.
- Now drag down the formula through the column.
- The result is shown in the picture.
TEXT ALWAYS GOES INSIDE “”. IF TEXT IS NOT INSIDE THE INVERTED COMMAS , IT’LL CREATE AN ERROR.
ROTATE TEXT IN EXCEL
Let us learn to rotate text in Excel.
This can be the requirement of any report where we want to put the text as vertical or horizontal or at an angle.
It is quite simple as there is an option given for this in the Excel.
STEPS TO ROTATE TEXT IN EXCEL:
- Select the text which we want to rotate.
- Go to HOME>ALIGNMENT>ORIENTATION
- Click it and choose the angle for rotation . If we want to rotate to a specific angle, choose the option FORMAT CELL ALIGNMENT as shown in the picture below.
EXTRACT TEXT FROM A CELL
We can extract the text from a cell in many ways.
Without VBA, we can use our standard LEFT, MID AND RIGHT FUNCTIONS to extract the text from the cell in Excel.
LEFT FUNCTION has the capability to pick the specified number of characters from the left side of the text.
The syntax is =LEFT(CELL CONTAINING TEXT,NUMBER OF CHARACTERS FROM THE LEFT)
RIGHT FUNCTION can extract the specified number of characters from the right whereas
The syntax is =RIGHT(CELL CONTAINING TEXT,NUMBER OF CHARACTERS FROM THE RIGHT)
MID FUNCTION takes out the specified number of the characters from the middle of the text.
The syntax is =MID(CELL CONTAINING TEXT,STARTING POSITION OF THE TEXT TO BE EXTRACTED, NUMBER OF CHARACTERS TO BE EXTRACTED)
The example shows the usage.
CHECK IF CELL CONTAINS TEXT OR NUMBER
This is again a part of the conditional assessment.
What if we want to know whether the cell contains a number or the text.
Well , it is very easy because Excel provides us with a direct function for the same. Or even if there were no function, we would have done it by playing with the codes.
FUNCTIONS ARE ALSO MADE FROM THE BASIC ENTITIES OF ANY PROGRAMMING LANGUAGE. IF WE KNOW THE BASICS, WE CAN CREATE OUR OWN FUNCTION.
Let us take an example and check if the cell contains a number or the Text.
So we have direct functions ISNUMBER() and ISTEXT().
If ISNUMBER(CELL CONTAINING VALUE)=TRUE that means the cell contains the number.
If ISTEXT(CELL CONTAINING VALUE)=TRUE that means the cell contains the TEXT.
Let us take an example.
Suppose a column is there with a mix of text and numbers.
Let us check first, whether the cell contains a number and in the next column let us check if the cell contains text.
VALUES |
WHAT |
12 |
65 |
HELLO |
GYANKOSH.NET |
STEPS TO USE ISNUMBER OR ISTEXT.
- Select the cell where we want to get the output.
- Put the formula as =ISNUMBER(CELL CONTAINING THE VALUE) to check if the value is a number.
- Similarly put the formula as =ISTEXT(CELL CONTAINING THE VALUE) to check if the value is a Text.
- The result will be displayed as TRUE or FALSE.
COUNT CELLS WITH SPECIFIC TEXT
Let us find out this trick to count the cells with specific text.
In this task we need the following functions.
1. A function which can Count the specific items. COUNT FUNCTION would work for this.
For simple counting, this function would suffice.
Let us try with an example.
Suppose we have the following data with us.
WORDS |
DATE |
MATE |
FATE |
LATE |
BEAUTIFUL |
UGLY |
HELLO |
BUFFALO |
Let us find out the number of words with the text “ate” and “ly” and “lo”
STEPS TO COUNT THE NUMBER OF WORDS WITH SPECIFIC TEXT:
Select the cell where we want to find out the count with the text “ate”.
Enter the formula as =COUNT(ARRAY WITH ALL THE CELLS,”*ate”). For our example the exact formula will be
NUMBER STORED AS TEXT PROBLEM IN EXCEL
Have you ever noticed that your cell contain a number [ visibly number] but it is not responding to your formulas or functions as expected.
Most probably it is because of the format of the cell or NUMBER TYPE OF THE CELL.
Out of many problems with the formatting of the cell , we are going to focus on the one called NUMBER STORED AS TEXT.
Sometimes or we say most of the time when we copy any number from somewhere or get the data through any channel, we get this problem that the NUMBER IS VISIBLE AS A NUMBER BUT THE FORMAT IS TEXT.
The problem with such numbers is that they won’t respond to our calculations. Look at the picture below.
As we can see from the picture above that the text is not responding to the functions. [Although Excel is smart enough to do the simple tasks like addition , subtraction etc. even with the number stored as text]
HOW TO RECOGNIZE IF THE NUMBER IS STORED AS TEXT?
That is pretty simple. Excel itself marks it by a small TRIANGLE AT THE LEFT UPPER CORNER.
HOW TO CONVERT NUMBER STORED AS TEXT , BACK TO NUMBER?
Just click the TRIANGLE MENTIONED ABOVE.
Excel will give you a number of options like
Choose CONVERT TO NUMBER.
The numbers will be converted from text to numbers.