How to Use Left Trim Function in Excel (7 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Fortunately, we will discuss doing left trim in Excel using both functions and VBA. Left trim can be of various types. For example, you can remove a character from the left side of a string. On the other hand, you can also erase leading spaces (Space present on the leftmost side of a string) from a data string. In this article, I will show 7 suitable ways to use the left trim function in Excel.

Here, we have used the RIGHT function to get 4 characters from the right side. This trims the left characters automatically. We’ll demonstrate some other ways to use the left trim function in Excel.

overview of using right function to trim left side


 

How to Use Left Trim Function in Excel: 7 Suitable Ways

Often, we need to use left trim function for certain business analytics, and the process becomes more interesting with Excel. However, the task is easy and simple. But you will need an arrangement in order to perform the operation properly. For the purpose of demonstration, I have used the following sample dataset. Here, the dataset contains Product Code and we will trim Color from the code.

sample dataset to use left trim function in excel


1. Apply RIGHT Function to Trim Left Side Characters in Excel

When we work with spreadsheets, often, we need to trim the data from the left. For instance, you have a code containing color, reference no, size, etc. and you want to separate the code according to your preference. The easiest way to trim the left portion of data is to use the RIGHT function. The RIGHT function returns the specified character from the end of the text string. For instance, we want to trim the value of Cell B5. Here are the steps required:

Steps:

  • Write the following formula to separate the color from the code.
=RIGHT(B5,4)

Apply RIGHT Function to Trim Left Side Characters in Excel

  • After entering the formula, the color Blue will be separated. Use the Fill Handle (+) to copy the formula to the rest of the cells.

use of right function in excel

Read More: How to Trim Right Characters and Spaces in Excel 


2. Application of  REPLACE Function to Remove Left Side Characters in Excel

Another easy to left trim is to use the REPLACE function. Usually, REPLACE function replaces part of a text string with a different text string. However, ee have applied the function to the previous set of data and here are the steps followed:

Steps:

  • Firstly, type the following Formula:
=REPLACE(B5,1,C5,"")

Application of  REPLACE Function to Remove Left Side Characters in Excel

  • Upon entering the formula, here is the result we got:

use of replace function in excel


3. Use VBA to Trim Left Side Characters

If you do not want to use any function or formula to do the left trim, using VBA can be an option in excel. However, we have used the following steps for the VBA:

Steps:

  • Following is the string we want to trim:

Use VBA to Trim Left Side Characters

  • First, go to the corresponding sheet. Then, right-click on the sheet name and select the View Code option to bring the VBA window.
  • Now, write the following code in the Module.
Option Explicit
Sub GetString()
Dim str1 As String
str1 = "3PastelBlue"
MsgBox Right(str1, 10)
End Sub

Use VBA to Trim Left Side Characters

  • Finally, run the code and you’ll get the code trimmed.

VBA dialog box


4. Delete Characters from Left Using RIGHT and LEN Function

Likewise, methods mentioned above, we can remove characters from the left part of the data by using the combination of RIGHT and LEN functions. The LEN function returns the number of characters in a text string. Steps associated with this method are:

Steps: 

  • At first, type the following formula.
=RIGHT(B5,LEN(B5)-C5)

Delete Characters from Left Using RIGHT and LEN Function

Breakdown of the Formula:

  • LEN(B5)

Here, the LEN function returns the number of characters in a text string.

  • RIGHT(B5,LEN(B5)-C5)

In this formula, the number of characters trimmed from the left is subtracted from the whole string. The formula will return the string trimmed from the left.

  • Finally, the output is:

combination of right and len functions in excel


5. Remove Leading Spaces Using the Combination of FIND, MID, TRIM & LEN Functions in Excel

Often, when we copy data from websites, many unnecessary spaces are also copied with the data. But, finding these spaces one by one will be time-consuming. Fortunately, there are some combinations of functions available to trim these spaces. In this method, we will use the combination of FIND, MID, TRIM & LEN functions to remove the leading space. The steps involved are:

Steps: 

  • To remove the spaces, type the following formula:
=MID(B5,FIND(MID(TRIM(B5),1,1),B5),LEN(B5))

Remove Leading Spaces Using the Combination of FIND, MID, TRIM & LEN functions in Excel

Breakdown of the Formula:

  • LEN(B5)

This formula will return the number of characters in Cell B5.

  • TRIM (B5)

The TRIM function removes all spaces from B5 except for single spaces between words.

  • MID(TRIM(B5),1,1)

The MID function returns characters from the middle of B5, given the starting position and length.

  • FIND(MID(TRIM(B5),1,1)

The FIND function returns the starting position of one text string within another text string. Here the formula returns the position of the character we found in the previous procedure.

  • MID(B5,FIND(MID(TRIM(B5),1,1),B5),LEN(B5))

When applying the formula with another MID function, it will erase the spaces from only the left side of the string.

  • In the end, the output is:

combination of find, mid, trim and len functions in excel

Read More: How to Trim Spaces in Excel


6. Apply Combination of REPLACE, LEFT, FIND & TRIM Excel Functions to Remove Leading Spaces

Likewise, the previous method, the combination of REPLACE, LEFT, FIND, and TRIM functions can be used to erase leading spaces. However, we have followed the below steps to complete the task:

Steps: 

  • Using the abovementioned functions, type the following formula first:
=REPLACE(B5,1,FIND(LEFT(TRIM(B5),2),B5)-1,"")

Apply Combination of REPLACE, LEFT, FIND & TRIM Excel Functions to Remove Leading Spaces

Breakdown of the Formula:

The combination of FIND, LEFT, and TRIM functions help us to calculate the position of the first space character in the string; spaces towards the left side of the string. Here, we passed the formula through REPLACE function. As a result, the leading spaces of the string were replaced with no blank (“”). The formula will erase only the leading spaces from the string.

  • Lastly, The result is:

combination of replace, find, left and trim functions in excel


7. Use VBA to Remove Leading Spaces in Excel

Leading spaces can be deleted using the VBA also.

Steps:

  • Firstly, select the cells with leading spaces.

Use VBA to Remove Leading Spaces in Excel

  • Secondly, go to the corresponding sheet, right-click on the sheet name, and select the View Code option to bring the VBA window.
  • In the Module, write the following code:
Option Explicit
Sub TrimExample1()
Dim Cell As Object
Dim Rg As Range
Set Rg = Selection
For Each Cell In Rg
Cell.Value = Trim(Cell)
Next Cell
End Sub

Use VBA to Remove Leading Spaces in Excel

  • Lastly, run the code and get the string without leading spaces.

excel vba to remove leading space result


Download Practice Workbook

You can download the practice workbook that we’ve used to prepare this article.


Conclusion

In the article, I have discussed widely used methods to trim the left characters in Excel. You can learn both about the methods related to function and VBA. Use these methods, whenever suitable, and let us know if you have any queries.


Related Articles


<< Go Back to Excel TRIM Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo