Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Truncate Text from Left in Excel (5 Suitable Ways)

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.

Dataset to Truncate Text from Left in Excel


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.

Use Excel LEFT Function to Abbreviate Text from Left

  • 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.

 Application of Left Function to Truncate Text from Left in Excel

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.

Combine LEFT & LEN Functions to Truncate Text from Left

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)


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.

Employ MID Function to Truncate Text from Left in Excel

  • 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.

Apply Excel Flash Fill Feature for Shortening Text from Left

  • Subsequently, press ENTER to get the result.
  • Finally, you will get all the starting months of employees.

Note: This method will work only in Excel 365 version.

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.

Truncate Text from Left by Employing VBA Code in Excel

  • 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.

Using Defined Function to Truncate Text from Left in Excel

  • 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.


Practice Section

Now, you can practice the explained method by yourself.

Practice Section to Truncate Text from Left in Excel


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.


Related Articles

Musiha

Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo