Left TRIM in Excel (Table of Contents)
Left TRIM in Excel
The Excel LEFT function extracts a certain number of characters from the left side of a text string. Excel’s TRIM function removes extra spaces from the text. It removes all spaces and in-between spaces except for a single word or space character.
Leading Spaces:
- Extra spaces before text strings in cells are called as Leading Spaces (Extra Spaces towards the Left side)
- When you copy a text from outer sources like web pages, database or any external source, and paste it in 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 right, 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. As of now, 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 formula’s where it removes only the leading spaces, irrespective of trailing spaces & extra in-between spaces present in it.
1. We can get rid of only leading space in a text with the help of or combination of FIND, MID, TRIM & LEN excel functions.
=MID(A2,FIND(MID(TRIM(A2),1,1),A2),LEN(A2))
2. TRIM function is used along with LEFT, FIND and REPLACE functions to remove only 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 or 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 the column A i.e. Address in a cell “A2” which it 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 left side) in a word or a text in a cell.
- In the above example, Address in cell “A2” contains duplicated and triplicated spaces in between a word, so that it makes sense to read your data better & present it.
- Here, I need to only remove leading extra space (i.e. the spaces towards the left side) retaining extra spaces in between a word
- To use a combination of formula to get rid of only leading space in a text with the help of or 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 out to 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 (LEN function calculates the string length) starting at the position of the first text character. Which removes only 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 in 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 formula to get rid of only leading space in a text.
- TRIM function is used along with LEFT, FIND and REPLACE functions to remove only leading spaces.
Let’s check out the breakup of the above formula, how it works?
FIND, LEFT and TRIM function in excel helps out to 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 leading space in a text.
- Apply the formula in a cell “B2”
- Once you apply this formula the result you get is shown below.
- Similarly, this formula is applied in the cell B3 to remove only leading space in the 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 text value which 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, there is no other extra in-between spaces or trailing spaces.
- Apply 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 Leading Spaces of a Text String along with practical examples and downloadable excel template. You can also go through our other suggested articles –
