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.
