Musiha Mahfuza Mukta
When you have a big dataset with text as cell values, then you may need to shorten the text in Excel. Basically, you can truncate a text either from left, from right, or even from the middle. So, if you are looking for how to truncate text from left in Excel, then you have come to the right place. Today, in this article, I’m going to explain how to truncate text from left in Excel.
Furthermore, for conducting the session, I will use Microsoft 365 version.
Download Practice Workbook
You can download the practice workbook from here:
5 Easy Methods to Truncate Text from Left in Excel
Here, I will demonstrate 5 suitable methods with detailed steps on how to truncate text in Excel. For your better understanding, I am going to use the following dataset. Which contains two columns. Those are Employee Name and Working Period. The dataset is given below.
1. Use Excel LEFT Function to Abbreviate Text from Left
You can apply the LEFT function to truncate text from left in Excel. In my dataset, the Working period of every employee is given. At this time, suppose, I want to find out only the joining month of every employee. So, I need to truncate the whole working period into starting month. Now, follow the steps given below.
Steps:
- Firstly, you must select a new cell D5 where you want to keep the truncated text.
- Secondly, you should use the formula given below in the D5 cell.
=LEFT(C5,3)Here, in this formula, I have used only the LEFT function. This function will return a particular number of characters from the start of the text. Where C5 is that text and it will return 3 characters from the leftmost character of that text.
- After that, you must press ENTER to get the result.
- Now, you can drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D13.
- Lastly, you will get all the joining months of employees.
Read More: How to Truncate Date in Excel (4 Simple Methods)
2. Combine LEFT & LEN Functions to Truncate Text from Left
Here, I will use a combination of LEFT and LEN functions to abbreviate the text from left in Excel. So, follow the steps given below.
Steps:
- Firstly, you must select a new cell D5 where you want to keep the truncated text.
- Secondly, you should use the formula given below in the D5 cell.
=LEFT(C5, LEN(C5) - 17)- Thirdly, press ENTER.
Formula Breakdown
- Here, LEN(C5) will give the total number of characters of the text situated in the C5 cell.
- Output: 30.
- Then, 30-17 is a simple subtraction.
- Output: 13.
- Lastly, the LEFT(C5,13) function will return a particular number of characters from the start of the text. Where C5 is that text and it will return 13 characters from the leftmost character of that text.
- Output: August, 2012.
- Then, you can drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D13.
- Finally, you will get all the short text.
Read More: How to Truncate Text After Character in Excel (4 Suitable Ways)
Similar Readings
- Truncate Numbers in Excel (8 Suitable Methods)
- How to Use INDEX MATCH with Excel VBA
- 6 Best Excel VBA Programming Books (For Beginners & Advanced Users)
- Import Data from Website to Excel with VBA Code
- How Different Is VBA from Other Programming Languages
3. Employ MID Function to Truncate Text from Left in Excel
Here, you can apply the MID function to truncate text from left in Excel. Through my dataset, you can see the Working period of every employee. At this time, suppose, you want to find out only the joining month of every employee. So, you need to shorten the whole working period into the starting month. Now, follow the steps given below.
Steps:
- Firstly, you must select a new cell D5 where you want to keep the result.
- Secondly, you should use the formula given below in the D5 cell.
=MID(C5,1,3)Here, in this formula, I have used only the MID function. This function will return a particular number of characters from a defined character place in the text. Where C5 is that text and it will return from the 1st character to the 3rd character.
- Thirdly, press ENTER to get the result.
- Then, you can drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D13.
- As a result, you will get all the starting months of employees.
Read More: How to Truncate Text from Right in Excel (5 Easy Methods)
4. Apply Excel Flash Fill Feature for Shortening Text from Left
In this method, I’m going to use the Flash Fill feature to truncate text from left in Excel. Actually, this is an interesting and simple way. Basically, you will truncate the text into separate columns by following a Pattern. Now, let’s see the steps given below.
Steps:
- Firstly, you have to write the target result manually up to which you can see Excel’s suggestion. For example, I have written “Aug”, and “Ma”, and then I got the suggestion.
Actually, you have to do this to show Excel a pattern. So, when Excel understands your pattern then Excel will suggest you the output. Below, I have attached the image.
- Subsequently, press ENTER to get the result.
- Finally, you will get all the starting months of employees.
Read More: How to Stop Excel from Truncating Text (3 Easy Methods)
5. Truncate Text from Left by Employing User-Defined Function in Excel
The most interesting part is that you can build your own function to truncate text from left in Excel. Moreover, you can employ the VBA code to develop a defined function. The steps are given below.
Steps:
- Firstly, you have to choose the Developer tab >> then select Visual Basic.
- Now, from the Insert tab >> you have to select Module.
- At this time, you need to write down the following Code in the Module.
Function Truncate_Txt_frm_Left(my_Text As String, removed_num_of_string As Long)
Truncate_Txt_frm_Left = Left(my_Text, Len(my_Text) - removed_num_of_string)
End Function
Code Breakdown
- Here, I have created a Function named Truncate_Txt_frm_Left.
- Next, I have declared a variable my_Text as a String to call the text, and another variable removed_num_of_string as Long to insert a number.
- Then, using the Left and Len functions, I developed the function named Truncate_Txt_frm_Left.
- Now, you have to save the code by pressing CTRL+S and the code extension will be .xlsm.
- Then, you need to go to the Excel worksheet.
At this time, you can use your defined function. For this, you should follow the given steps.
Steps:
- Now, you have to select a cell, where you want to keep the result. I have selected the D5 cell.
- Then, type “=tr” and you will find your defined function.
- At this time, use the corresponding formula in the D5 cell.
=Truncate_Txt_frm_Left(C5,17)Here, this function will return a particular number of characters from the start of the text. Where C5 is that text and it will return 17 characters from the leftmost character of the text.
- Then, you must press ENTER to get the result.
- Now, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D13.
- Finally, you will get all the truncated text.
Read More: How to Write VBA Code in Excel (With Easy Steps)
Practice Section
Now, you can practice the explained method by yourself.
Conclusion
I hope you found this article helpful. Here, I have explained 5 suitable methods to Truncate Text from Left in Excel. You can visit our website Exceldemy to learn more Excel-related content. Please drop comments, suggestions, or queries if you have any in the comment section below.
