While working in SAP, many times I came across situation where I got a list of material master with more than 50000 records and I had to find out and remove unwanted space at the end or starting of the text. I always used the TRIM function to do that. Today I am sharing the working the functionality of TRIM function in MS excel.
TRIM function in MS excel is an important formula to remember for every SAP consultant. Many times master data such as Material master gets uploaded in SAP with unwanted “space”. With such unwanted “space” in master data user gets error while sending data to a third party system. Since most of the middle wares and third party systems are not designed to handle such “space” we get error.
Microsoft has specially designed the TRIM function to handle such cases. In fact, Microsoft has mentioned in their help function (F1) that this function is designed to remove irregular spacing from another application.
Here is the step by step process to use this formula:
Step 1 : Keep the text in one column
Step 2 : Now we will apply formula on the cell next to the text.
1. Select the formula
2. Click OK to insert the formula in the excel sheet
Step 3 : Once the formula dialog appears on the screen follow there steps
1. Select the text from where we have to remove the unwanted spaces.
2. In the formula screen you can clearly see the unwanted spaces in the inverted commas
3. Result is highlighted in blue color. You can see that unwanted spaces are removed.
4. Now click on OK button to get the result on excel sheet.
Result screen :
This is how we can apply the TRIM function in MS excel. I hope this article helped you.
Vaibhav is an engineer by schooling and an SAP Consultant by training with 12 years of experience. He is certified in SAP PM and SAP MM. His expertise includes SAP PM, SAP MM, SAP QM, and MS excel. His industry specialties are Chemical, Automobile, Electronics and Infrastructure. He has lots of experience in SAP Training and Consulting.