In this tutorial, I will describe the functions used to cut pieces of text – LEFT, RIGHT, and MID.
One of the common tasks in Excel is cutting text, which means removing a portion of text from a cell or string of text. Cutting text can be useful for cleaning up data, separating information into different cells, or extracting specific information from a larger dataset.
How to cut text?
In this article, we will explore different methods to cut text in Excel, including using the Cut command, LEFT, RIGHT, and MID functions. These techniques will help you save time and improve the accuracy of your data analysis and reporting.
In all of the features listed below the text argument, unless it is a reference to a cell or the formula returns the text must be enclosed in quotes!
Cutting Text Using Formulas
To trim characters from the beginning of a cell, utilize the LEFT function. It trims text from the specified length, starting from the left side. The syntax is:
=LEFT(cell reference, number of characters)
Replace “cell reference” with the cell containing text and “number of characters” with the desired count.
For trimming from the end, employ the RIGHT function. It removes text from the end, based on the given length. The syntax is:
=RIGHT(cell reference, number of characters)
Again, replace “cell reference” and “number of characters” accordingly.
The num_chars argument in both functions is optional. If ignored, it defaults to one character—first for LEFT, and last for RIGHT. If num_chars exceeds text length, the entire text is returned.
To cut characters from the middle, use the MID function. It extracts characters starting from a specific location. The syntax is:
=MID(cell reference, starting position, number of characters)
Replace “cell reference,” “starting position,” and “number of characters” with the appropriate values. For instance, to start from the beginning, set starting position to 1. The num_chars argument behaves similarly to the previous functions.
Cutting Pieces of Text After Particular Sign
In this example you will teach yourself how to cut a piece of text after some particular sign.
Sometimes it happens that you receive some strange data. There are some part which you don’t need and it is hard to get rid of them. In this example you have data which begins with product code. Content which you need is after coma (,) sign.
There is a way in Excel to extract substrings. Just use this formula for: =MID(A2,FIND(“,”,A2)+1,LEN(A2)-FIND(“,”,A2)+1)
Paste this formula to your cell to extract data from A1 cell. Drag and drop down to extract data from another cells in A column.
Different formula I know is =TRIM(MID(A2,FIND(“,”,A2,1)+1,100))
It works the same way. There is another proof that you can do the same things in different ways within Excel.