Md Shamse Tabrej
The LEFT function in Excel takes a text string and removes a specified number of characters from its left side. In addition, the TRIM function in Excel eliminates unnecessary white space. Moreover, it only leaves one space between words or characters if there are none. Hence we’ll try how to apply left trim function in Excel and we will go through two methods, function and VBA, for performing a left trim in Excel. Likewise, one’s left trim can be styled in a number of ways.
📁 Download Excel File
Download the free workbook here.
Overview of TRIM Function in Excel
Eventually, This function strips the text of all punctuation and spaces, leaving only single spaces between words. If you’ve imported text from another program that has weird spacing, you can fix it with the TRIM function.
Syntax:
=TRIM(text)
Text: The text that you wish the spaces removed from.
For example, =TRIM(” First Semester Result”) denotes the function that will eliminate the leading and training text according to the formula and will show an output like “First Semester Result”.
Learn How to Apply Left Trim Function in Excel with These 7 Ways
Especially we will accomplish learning the usage of the TRIM function to trim the left-side characters in seven different ways. To do that, we will consider a dataset containing information about student ID. We will try to extract the name of the student from the ID.
1. Trim Characters of Left Side Using RIGHT Function
In this method, we will use the RIGHT function to trim the characters on the left side.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select the blank cell C6.
- Secondly, insert the following formula:
=RIGHT(B6,5)
- Finally, press Enter key and drag the fill handle to see the result after trimming.
After doing this you will get an output.
Thus you can trim the character from the left side.
2. Remove Left Side Characters Using REPLACE Function
The REPLACE operation is another simple way to trim the left side. The REPLACE function allows you to swap out a portion of one string of text for another. The following are the procedures we followed as we applied the function to the prior data set:
⬇️⬇️ STEPS ⬇️⬇️
- At first, write the following formula in blank cell D6.
=REPLACE(B6,1,C6,””)
- Now press Enter and drag the fill handle to see the output.
This allows you to remove characters from the left side of the input text.
3. Trim Left Characters Using VBA Code
Excel’s Visual Basic for Applications (VBA) can be useful if you don’t want to utilize any functions or formulas to perform the left trim. For the VBA, we followed these instructions:
⬇️⬇️ STEPS ⬇️⬇️
- To open the VBA window, locate the appropriate sheet by name, right-click, and choose View Code.
- Insert the following code in the module:
Sub GtString() Dim sl As String sl = "101David" MsgBox Right(sl, 5) EndSub
This is the outcome you will get after adding the code.
You are able to remove characters from the left side as a result.
4. Remove Left Characters by Applying RIGHT and LEN Functions
In a similar vein to the aforementioned techniques, we can use the combination of the RIGHT and LEN functions to exclude characters from the left side of the data. To find out how many characters are in a string, use the LEN function. So the procedures involved in this approach are:
⬇️⬇️ STEPS ⬇️⬇️
- Select cell D6 and type the formula given below:
=RIGHT(B6,LEN(B6)-C6)
- Finally, hit Enter and drag the fill handle and you will see the results.
As a result, you can trim the character from the left side.
🔨 Formula Breakdown
👉 Criteria = LEN(B6)-C6 : will return the number characters after trimming.
👉 Criteria = RIGHT(B6,LEN(B6)-C6) : will return the specified number of characters after trimming the text.
5. Applying REPLACE, LEFT, FIND & TRIM Functions Combined to Remove Leading Spaces in Excel
We frequently copy several extra spaces along with the data when we copy from websites. However, it will take time to individually locate each of these nooks. Thankfully, there are a few different ways to combine functions to reduce these gaps.
To accomplish this, we will utilize the power of the FIND, MID, TRIM, and LEN functions in combination with one another. Here’s what has to be done:
⬇️⬇️ STEPS ⬇️⬇️
- Write down the following formula at empty cell C6.
=MID(B6,FIND(MID(TRIM(B6),1,1),B6),LEN(B6))
- Now press Enter key and drag the fill handle.
Because of this, you can remove the leading space of the text.
🔨 Formula Breakdown
👉 Criteria = LEN(B6) : will return the number character in cell B6.
👉 Criteria = TRIM(B6),1,1),B6) : will remove the spaces from B6.
👉 Criteria = MID(TRIM(B6),1,1),B6),LEN(B6)) : will return the character from the mid of the text in the given position.
👉 Criteria = FIND(MID(TRIM(B6),1,1),B6),LEN(B6) : will return the precise starting position of the string.
6. Using FIND, MID, TRIM & LEN Functions Combined to Delete Leading Spaces
Similar to the preceding approach, you can get rid of leading spaces by using the combination of the REPLACE, LEFT, FIND, and TRIM functions. These are the procedures we followed to do the task:
⬇️⬇️ STEPS ⬇️⬇️
- Firstly select cell C6.
- Insert the following formula:
=REPLACE(B6,1,FIND(LEFT(TRIM(B6),2),B6)-1,””)
- Finally, press Enter to see the results.
Thus, you may remove the leading space.
🔨 Formula Breakdown
👉 Range = TRIM(B6) : This will remove the blank space from text except for a single space in cell B6.
👉 Criteria = LEFT(TRIM(B6),2) : returns a specific number from the initial point of the B6 string.
👉 Criteria = FIND(LEFT(TRIM(B6),2),B6) : returns starting position of one text string in another string.
👉 Criteria = REPLACE(B6,1,FIND(LEFT(TRIM(B6),2),B6)-1,””) : will replace leading space of the string.
7. Using VBA Code for Removing Leading Spaces
The VBA also allows for the removal of leading spaces. For this follow the steps below:
⬇️⬇️ STEPS ⬇️⬇️
- Choose the cells that have leading spaces.
- To bring up the VBA window, navigate to the relevant sheet, right-click the sheet name, and choose View Code.
- Insert the following code:
Sub TrimExample1()
Dim cl As Object
Dim rg As Range
Set rg = Selection For Each cl
In rg cl.Value = Trim(cl)
Next cl End Sub
- This permits the elimination of the leading space of the text.
- And finally, you got rid of unnecessary leading space
What to Do When TRIM Is Not Working in Excel
Reason: In the 7-bit ASCII character set, the space character is represented by code value 32, and the TRIM function simply removes this character. The non-breaking space is an additional space character in the Unicode character set and a popular HTML character. The decimal value of the nonbreaking space is 160, and TRIM cannot remove it on its own.
Solution: However, The SUBSTITUTE function can be used to transform non-breaking spaces into normal spaces before they are trimmed using the TRIM function if your data collection has any white spaces that the TRIM function fails to eliminate.
📄 Important Notes
🖊️ Check whether you’ve used Absolute Reference when it was needed.
🖊️ Always Search for simple ways to tackle different kinds of situations.
🖊️ Try to use the shortcut of the keyboard to increase your efficiency.
📝 Takeaways from This Article
📌 You can trim the left character of a text.
📌 In addition, you can trim the leading space of a text.
Conclusion
To summarize, I have included numerous approaches on how to apply the left trim function in Excel in the post. Moreover, You can get knowledge of both VBA and function-related approaches. Additionally, use these techniques as appropriate, and contact us if you have any questions. Also, visit ExcelDen.com to read more articles on Excel.
