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.