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 how we can extract any fragment of text from the given text.
WHEN THE EXTRACTION IS NEEDED
Extracting the text is a very basic requirement while creating any reports.
EXTRACTION OF TEXT IS THE PROCESS OF TAKING OUT A PORTION OF THE TEXT WHICH CAN VARY FROM ONE CHARACTER TO MANY LETTERS FROM THE GIVEN TEXT.
The text can be extracted easily with the standard LEFT, RIGHT AND MID FUNCTIONS.
EXTRACT TEXT FROM A CELL
CONCEPT:
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)
EXAMPLES
Let us take a few examples to learn how to extract text from the cell.
STEPS TO EXTRACT TEXT FROM CELLS:
- Select the cell where we want the result.
- FOR TEXT FROM LEFT: Use LEFT function to extract data from the left. Use the function as =LEFT(CELL CONTAINING TEXT, NUMBER OF CHARACTERS)
- FOR TEXT FROM RIGHT: Use RIGHT function to extract data from the right. Use the function as =RIGHT(CELL CONTAINING TEXT, NUMBER OF CHARACTER)
- FOR TEXT FROM THE MIDDLE PORTION: Use MID function to extract data from anywhere in the TEXT. Use the function as =MID(CELL CONTAINING TEXT, CHARACTER POSITION FROM WHERE THE TEXT PICKING WILL START, NUMBER OF CHARACTERS TO BE EXTRACTED.)
- The Text , Extracted text and formula are shown in the picture below.
EXPLANATION:
If you look at the picture above we have taken a few examples there to extract the text from the given TEXT.
The sample text is given under the heading TEXT.
- Extract HELLO from the given text HELLO AND.
We can see that HELLO is the starting word in the sentence [ or rather a group of words].
We can easily extract the word from the given text by using the LEFT FUNCTION.
The formula use is =LEFT(E5,5)
We’ll count the number of character which comes out to be 5 for this case.
2. Extract TO from the given text WELCOME TO.
In this case, we can see that TO is easily accessible from the RIGHT SIDE. So we can easily use the function RIGHT to extract 2 letters from the right direction.
So the formula used as =RIGHT(E6,2) extracts the desired TO from the given text.
3. Extract KOSH.NET from the given text GYANKOSH.NET
In this case, we can see that we need to extract the text from the mid although can be accessed from the Right too.
We’ll make use of MID function to extract this .
The function is used as =MID(E7,5,8)
This formula/function will extract the desired portion KOSH.NET from the text and put it in the result cell.
4. Extract THANKS from the given text THANKS FOR VISITING.
This is again the example of case when we have the easy access from the LEFT.
So we used the simple function LEFT as =LEFT(E8,6)
which will extract the word THANKS from the given text snippet THANKS FOR VISITING.
