Updated August 16, 2023
Left TRIM in Excel
Excel’s TRIM function removes extra spaces from the text. It removes all spaces and in-between spaces except for a single word or area character. The Left Trim function in Excel extracts a certain number of characters from the left side of a text string.
Leading Spaces:
- Extra spaces before text strings in cells are called Leading Spaces (Extra Spaces towards the Left side)
- When you copy text from outer sources like web pages, databases, or any external source and paste it into Excel, it contains leading space, which is very difficult to point out with a naked Eye.
- Usually, when we use the trim function, it removes extra spaces from the right and left sides, and it also removes extra spaces between words intact.
- In certain scenarios, you want to remove only the leading spaces (i.e., the spaces towards the left side) in a word or a text in a cell. Currently, there is no Excel formula to remove specifically or only leading or trailing spaces by keeping multiple spaces between words intact.
- Only Excel VBA has the option of LTRIM and RTRIM functions which will either remove all the spaces from the left side (leading) or from the right side (trailing).
- We need to create a formula in Excel to remove only the leading space in a text; currently, I have created two formulas where it removes only the leading spaces, irrespective of trailing spaces & extra in-between spaces present in it.
1. We can eliminate only the leading space in a text with the help of our combination of FIND, MID, TRIM & LEN excel functions.
=MID(A2,FIND(MID(TRIM(A2),1,1),A2),LEN(A2))
2.Use TRIM, LEFT, FIND, and REPLACE to count loans by removing leading spaces.
=REPLACE(A2,1,FIND(LEFT(TRIM(A2),2),A2)-1,””)
How to Remove Extra Leading Spaces in Excel?
Let us discuss How to Remove Extra Leading Spaces of a Text String in a Cell with the help of our combination of FIND, MID, TRIM & LEN Excel functions OR LEFT, FIND, REPLACE & TRIM functions.
Example #1
- In the below-mentioned example, I have a dataset in column A, i.e., Address in a cell “A2”, which contains different types of extra spaces, leading spaces, Trailing spaces & extra in-between spaces; here, I need to remove only the leading spaces (i.e., the spaces towards the left side) in a word or a text in a cell.
- In the above example, the Address in cell “A2” contains duplicated and triplicated spaces between a word so that it makes sense to read your data better & present it.
- Here, I need only to remove the leading extra space (i.e., the spaces towards the left side), retaining extra spaces between a word.
- Using a combination of formulas to eliminate only the leading space in a text with the help of our combination of FIND, MID, TRIM & LEN excel functions.
Let’s check out the breakup of the above formula; how it works?
- FIND, MID and TRIM function in Excel helps calculate the position of the first text character in a string. And later, when you supply that number to another MID function, it returns the entire text string (the LEN function calculates the string length) starting at the position of the first text character, which removes only the leading space in a text.
- Apply the formula in a cell “B2”.
- Once you apply this formula, the result you get is shown below.
In the Output or Result in the cell “B2”, only the leading space before the text is removed, retaining extra spaces between a word.
Example #2
- In the below-mentioned example, I have a dataset in column A, Address in cells “A2” & “A3”, which contains different types of extra spaces, leading spaces, Trailing spaces & extra in-between spaces.
- Here to, remove only the leading spaces (i.e., the spaces towards the left side) in a word or a text in a cell.
- I can use a combination of formulas to eliminate only the leading space in a text.
- Use TRIM, LEFT, FIND, and REPLACE to count loans by removing leading spaces.
Let’s check out the breakup of the above formula; how it works?
FIND, LEFT, and TRIM functions in Excel help calculate the position of the first space character in a string (i.e., the spaces towards the left side). And later, when you supply that to REPLACE function, it replaces space with no blank option & thereby removing only the leading space in a text.
- Apply the formula in cell “B2.”
- Once you apply this formula, the result you get is shown below.
- Similarly, this formula is applied in cell B3 to remove only the leading space in cell A3.
- In the resultant string, you can observe it has removed only spaces from the left side. Retaining extra spaces in between a word.
Things to Remember About the Left Trim in Excel
- Apart from these different formula combinations, you can create your own based on the nature of text value & its format.
- Suppose you have a text value that contains only leading space (It does not contain any Trailing spaces & extra in-between spaces in the word or text); Then, for this type of data, you can directly use the TRIM function, which deletes all the extra leading spaces in the text or word.
- In the above-mentioned table, you can observe, in the “A” column, i.e., US state data which contains only leading spaces, and there are no other extra in-between spaces or trailing spaces.
- Apply the TRIM function in the cell “A2“, i.e., =TRIM(A2).
- Once you apply this formula, the result you get is shown below.
- Drag the cell from A2 to A5.
- In the Result string, i.e., in the cell “A2”, you can observe Trim function has removed extra leading spaces in the word “Washington”.
Recommended Articles
This has been a guide to Left Trim in Excel. Here we discuss How to Remove the Leading Spaces of a Text String, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
