G
GARY
How can I delete all spaces after the last non-space character in each
cell?
cell?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
GARY
Excel's TRIM function does a little more than you mentioned, but it might
be what you want.
TRIM removes ALL extra spaces in the cells (not just at the end).
R
Rick Rothstein \(MVP - VB\)
Can you make use of a macro in your workbook? If so, right click the
worksheet tab, select View Code from the pop up menu that appears and
copy/paste the following into the code window that appears...
Sub RemoveTrailingBlanks()
Dim C As Range
For Each C In Selection
C.Value = RTrim(C.Value)
Next
End Sub
Go back to the worksheet, select all the cells you want to remove trailing
blanks from (it is alright to select a range containing cells both with and
without trailing blanks, the code will not be bothered by such a mixture),
key in Alt+F8, select RemoveTrailingBlanks from the list and click the Run
button.
Rick
TRIM removes ALL extra spaces in the cells (not just at the end).
worksheet tab, select View Code from the pop up menu that appears and
copy/paste the following into the code window that appears...
Sub RemoveTrailingBlanks()
Dim C As Range
For Each C In Selection
C.Value = RTrim(C.Value)
Next
End Sub
Go back to the worksheet, select all the cells you want to remove trailing
blanks from (it is alright to select a range containing cells both with and
without trailing blanks, the code will not be bothered by such a mixture),
key in Alt+F8, select RemoveTrailingBlanks from the list and click the Run
button.
Rick
Excel's TRIM function does a little more than you mentioned, but it might
be what you want.
TRIM removes ALL extra spaces in the cells (not just at the end).
P
Pete_UK
If those spaces are the non-breaking space character (with a code of
160), then TRIM and CLEAN will not remove them. But, you could use the
SUBSTITUTE function, or you can do it in one operation by highlighting
the cells affected, and CTRL-H (or Edit | Replace):
Find what: Alt-0160
Replace with: leave blank
then click Replace all,
where Alt-0160 means hold the Alt key down while you enter 0160 on the
numeric keypad.
Hope this helps.
Pete
160), then TRIM and CLEAN will not remove them. But, you could use the
SUBSTITUTE function, or you can do it in one operation by highlighting
the cells affected, and CTRL-H (or Edit | Replace):
Find what: Alt-0160
Replace with: leave blank
then click Replace all,
where Alt-0160 means hold the Alt key down while you enter 0160 on the
numeric keypad.
Hope this helps.
Pete
R
Rick Rothstein \(MVP - VB\)
If, as Pete has pointed out, you have non-breaking characters (ASCII 160) by
themselves, **or** in addition to trailing blanks, you can use this macro (a
modification of the one I posted earlier) to handle both...
Sub RemoveTrailingBlanks()
Dim C As Range
For Each C In Selection
C.Value = RTrim(Replace(C.Value, Chr(160), ""))
Next
End Sub
Rick
themselves, **or** in addition to trailing blanks, you can use this macro (a
modification of the one I posted earlier) to handle both...
Sub RemoveTrailingBlanks()
Dim C As Range
For Each C In Selection
C.Value = RTrim(Replace(C.Value, Chr(160), ""))
Next
End Sub
Rick