Remove leading and trailing spaces in a cell
To remove only the leading and trailing spaces from text in a cell we need to use the VBA TRIM function
Example: Remove leading and trailing spaces in a cell
Sub Remove_leading_and_trailing_spaces_in_a_cell()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'Remove leading and trailing spaces in cell (B5)
ws.Range("C5") = Trim(ws.Range("B5"))
ws.Range("C5") = Trim(ws.Range("B5"))
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Text: Have the text from which you only want to remove the leading and trailing spaces , populated in cell ("B5").
Worksheet Name: Have a worksheet named Analysis.
Text: Have the text from which you only want to remove the leading and trailing spaces , populated in cell ("B5").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Text: Select the text from which you only want to remove the leading and trailing spaces by changing the cell reference ("B5") to any range in the worksheet, that doesn't conflict with the formula.
EXPLANATION
To remove only the leading and trailing spaces from text in a cell we need to use the VBA TRIM function. The VBA TRIM function only removes the extra space at the start and end of the text in a cell. Therefore, any extra space between words in a cell will remain.