How to remove leading or trailing spaces in an entire column of excel worksheet in Excel2010 – Stack Overflow.
If you would like to use a formula, the TRIM function will do exactly what you’re looking for:
+---+-----------+--------------------+
| | A | B |
+---+-----------+--------------------+
| 1 | =TRIM(B1) | value to trim here |
+---+-----------+--------------------+So to do the whole column…
1) Insert a column
2) Insert TRIM function pointed at cell you are trying to correct.
3) Copy formula down the page
4) Copy inserted column
5) Paste as “Values”
Should be good to go from there…
If there are other characters than “spaces” in what you’re trying to remove, you could try =TRIM(CLEAN(B1)) which will remove all non-printable characters and any leading/trailing spaces.
MS Excel: TRIM Function (WS, VBA)
In Microsoft Excel, the TRIM function returns a text value with the leading and trailing spaces removed.
Syntax
The syntax for the TRIM function is:
TRIM( text )
text is the text value to remove the leading and trailing spaces from.
Applies To
- Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Type of Function
- Worksheet function (WS)
- VBA function (VBA)
Worksheet Function Example
Let’s take a look at an example to see how you would use the TRIM function in an Excel worksheet:
Based on the Excel spreadsheet above, the TRIM function would return the following:
| =TRIM(A1) | would return “Tech on the Net” |
| =TRIM(A2) | would return “1234” |
| =TRIM(A3) | would return “alphabet soup” |
| =TRIM(A4) | would return “www.techonthenet.com” |
| =TRIM(” apples “) | would return “apples” |
VBA Function Example
The TRIM function can also be used in VBA code in Microsoft Excel. For example:
Dim LResult As String
LResult = Trim (" Alphabet ")
The variable LResult would now contain the value of “Alphabet”.
Saidul Hassan