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.
THIS WAS AN EXCERPT FROM THE FIRST ARTICLE OF THIS SERIES MANIPULATING TEXT IN EXCEL – PART I
In this articles , we will continue learning many more techniques about the manipulation of text in Excel.
IN THIS ARTICLE WE WOULD LEARN TO TRUNCATE TEXT.
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.
TRUNCATE TEXT IN EXCEL
TRUNCATING TEXT in Excel means to cut it down from the various places as per our requirement.
We can take it further creating the task where we can remove any text portion after a certain character such as comma or space or full stop.
So, in Excel we have got some cutting tools which we already discussed in our previous post. [MANIPULATING TEXT IN MICROSOFT EXCEL PART I]
We can do so using the standard and powerful text truncating tools RIGHT, LEFT and MID functions easily.
There are complete articles on each of them how to use and other information. Visit them by clicking the FUNCTION NAME given here.
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.
We consider that you are familiar with the functions now. IF NOT CLICK ON THE FUNCTION MENTIONED ABOVE AND READ THE COMPLETE TOPIC.
Let us discuss a few examples how we can extract text.
LET US TAKE A TEXT FOR THE EXAMPLE.
“WOW! IT IS A WONDERFUL DAY” in cell E7.
EXAMPLE 1: TRUNCATE THE TEXT AFTER 5 LETTERS FROM THE LEFT.
GENERALIZED FORMULA TO BE USED =LEFT(CELL CONTAINING TEXT, NUMBER OF CHARACTERS TO BE KEPT FROM THE LEFT)
STEPS TO TRUNCATE TEXT AFTER 5 LETTERS FROM THE LEFT.
- Put the text in a cell.
- Select the cell where we want the result.
- Enter the formula =LEFT(E7,5)
- The result would appear as WOW! . [THERE IS A SPACE AFTER ‘!’]
- Look at the picture below for the description.
WHEN IT COMES TO CHARACTERS, SPACE ITSELF IS A CHARACTER. ALWAYS TAKE ITS CARE AS NEGLECTING THE SPACE CAUSES MUCH PROBLEMS SOMETIMES.
THE EXAMPLE.
“WOW! IT IS A WONDERFUL DAY” in cell E7.
EXAMPLE 2: TRUNCATE THE TEXT FROM THE LEFT AND REMOVE FIRST 5 LETTERS.
GENERALIZED FORMULA TO BE USED TO TRUNCATE THE TEXT FROM THE LEFT FOR A FIXED NUMBER OF CHARACTERS IS
=RIGHT(CELL CONTAINING TEXT, LEN(CELL CONTAINING TEXT) -NUMBER OF CHARACTERS TO BE KEPT FROM THE LEFT)
For the example, we’ll remove the first 5 characters from the left.
STEPS TO TRUNCATE TEXT FOR FIVE CHARACTERS FROM THE LEFT.
- Put the text in a cell.
- Select the cell where we want the result.
- Enter the formula =RIGHT(E7,LEN(E7)-5)
- The result would appear as WHAT A WONDERFUL DAY . [ THE REMOVAL HAS BEEN DONE FOR THE FIRST FIVE CHARACTERS].
- Look at the picture below for the description.
THE EXAMPLE.
“WOW! IT IS A. WONDERFUL DAY” in cell E18.
EXAMPLE 3: TRUNCATE THE TEXT AFTER A CERTAIN CHARACTER LIKE COMMA OR FULL STOP.
GENERALIZED FORMULA TO BE USED TO TRUNCATE THE TEXT FROM THE LEFT FOR A FIXED NUMBER OF CHARACTERS IS
=LEFT(CELL CONTAINING TEXT, SEARCH(” CHARACTER” , CELL CONTAINING TEXT, 1)
For the example, we’ll remove the text after the full stop.
STEPS TO TRUNCATE TEXT AFTER FULL STOP.
- Put the text in a cell.
- Select the cell where we want the result.
- Enter the formula =LEFT(E18,SEARCH(“.”,E18,1))
- The result would appear as WOW! IT IS A. [ THE REMOVAL HAS BEEN DONE FOR THE TEXT AFTER THE FULL STOP].
- Look at the picture below for the description and result.
This was an example for the ‘.’ full stop. But we can apply it to any type of special character or even spaces.