Mehedi Hasan
Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as per need and make databases for the future. Moreover, one interesting thing is that we need to truncate text from the right in Excel to better represent the data. But there are no direct methods to do so. However, I have used Microsoft Office 365 for the purpose of demonstration, and you can use other versions according to your preferences. In this article, I will show you 5 suitable methods to truncate text from right in Excel. Hence, read through the article to learn more and save time.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
5 Suitable Methods to Truncate Text from Right in Excel
In this tutorial, I will show you how to truncate text from the right in Excel. Here, I have used 5 different methods to make you understand the scenario properly. For the purpose of demonstration, I have used the following sample dataset.
1. Utilize RIGHT Function to Truncate Text from Right
In general, the RIGHT function is another popular function in MS Excel that is used to get the last character or characters in a text string based on the specific number of characters. In a word, this function is designed to return a specified number of characters from the rightmost side of a string. However, I will find the last part of each name from the dataset. Hence, go through the steps below.
📌 Steps:
- First of all, select cell C5 and write down the following formula.
=RIGHT(B5,6)
- Afterward, press Enter button and utilize the AutoFill tool to the entire column.
Read More: How to Truncate Text from Left in Excel (5 Suitable Ways)
2. Use Flash Fill Tool to Truncate Text from Right in Excel
In addition, you can utilize the Flash Fill tool in order to truncate text from the right easily. However, the process is quite easy and simple. So, go through the steps to complete the task properly.
📌 Steps:
- At first, write the right part of the name on cell C5.
- Afterward, insert the initial letter of the right part of the next name on cell C6. However, it will automatically show the rest of the part, including the cells below.
- Lastly, press the Enter key to get the final result.
Read More: How to Truncate Text After Character in Excel (4 Suitable Ways)
3. Truncate Text from Right with Text to Columns Feature
However, you can use the text to columns feature to return a specified number of characters from the rightmost side of a string. For example, I will truncate the last part of each name from the dataset. So, follow the steps below in order to complete the operation. For the purpose of demonstration, I have changed the sample dataset a bit, and I will use it for the rest of the part of the tutorial.
📌 Steps:
- Initially, select the dataset.
- Then, go to the Data tab and select Text to Columns.
- Next, check Fixed Width from Step 1 of the dialog box.
- After that, click on the position where you want to truncate the data and click Next.
- Now, select the part you want to truncate and insert the Destination.
- Afterward, click Finish.
Note:
Here, you have to select the Do not import column (skip) option if you don’t want all parts of your data. However, select General for the part you want to truncate.
- Finally, you can truncate text from the right side of your data.
Read More: How to Truncate Numbers in Excel (8 Suitable Methods)
4. Insert REPLACE Function to Truncate Text from Right
Furthermore, you can apply the REPLACE function to truncate text from the right. Usually, the function is used to replace a part of a text string with a different text string. Moreover, it returns with the new text string within which new and replaced text or word is present. However, read through the steps below.
📌 Steps:
- In the beginning, select cell C5 and insert the following formula.
=REPLACE(B5, 1, 6, "")
- In the end, hit the Enter key and apply the AutoFill tool to the whole column.
Read More: How to Stop Excel from Truncating Text (3 Easy Methods)
5. Truncate Text from Right Through Excel VBA
Last but not least, a VBA code can also help you to truncate text from the right in Excel. A VBA code is necessary for this method. However, you can just copy the code and run it in your Excel worksheet. Hence, go through the following steps in order to complete the task properly.
📌 Steps:
- Firstly, hold the Alt + F11 keys in Excel, which opens the Microsoft Visual Basic Applications window.
- Secondly, click the Insert button and select Module from the menu to create a module.
- Thirdly, a new window will open and write the following VBA macro in the Module.
Function TruncatefromRight(str As String, num_chars As Long)
 TruncatefromRight = Right(str, Len(str) - num_chars)
End Function
- Fourthly, write the formula below on cell C5.
=TruncatefromRight(B5,6)
- Finally, hit Enter button and use the AutoFill tool to the entire column.
Conclusion
These are all the steps you can follow to truncate text from right in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
For more information like this, visit Exceldemy.com.
