Remove leading and trailing spaces and extra spaces between words
To remove leading and trailing spaces from text and extra spaces between words and numbers, leaving only a single space between words and numbers we need to apply the Excel TRIM function
Example: Remove leading and trailing spaces and extra spaces between words
|
=TRIM("B5")
|
The formula removes the spaces at the start and end of the text in cell B5 and also removes the unnecessary spaces between words, leaving only a single space between words.
|
Sub Remove_leading_and_trailing_spaces_and_extra_spaces_between_words()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'Remove leading and trailing spaces and extra spaces between words
ws.Range("C5") = Application.WorksheetFunction.Trim(ws.Range("B5"))
ws.Range("C5") = Application.WorksheetFunction.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 TRIM.
Text: Have the text from which you want to remove the extra spaces in cell ("B5").
Worksheet Name: Have a worksheet named TRIM.
Text: Have the text from which you want to remove the extra spaces 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 want to remove the extra spaces by changing the cell reference ("B5") to any range in the worksheet, that doesn't conflict with the formula.
Explanation about the formula used to remove leading and trailing spaces and extra spaces between words
EXPLANATION
EXPLANATION
To remove leading and trailing spaces from text and extra spaces between words and numbers, leaving only a single space between words and numbers we need to apply the Excel TRIM function.
To remove leading and trailing spaces from text and extra spaces between words and numbers, leaving only a single space between words and numbers we need to apply the Excel TRIM function.
FORMULA
=TRIM(text)
=TRIM(text)
ARGUMENTS
text: The text from which to remove the spaces at the start and end of the text and unnecessary spaces between words and numbers.