Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting spaces
How can I delete all spaces after the last non-space character in each
cell? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting spaces
How can I delete all spaces after the last non-space character in each
cell? Excel's TRIM function does a little more than you mentioned, but it might be what you want. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting spaces
On Aug 17, 12:35*pm, MyVeryOwnSelf wrote:
How can I delete all spaces after the last non-space character in each cell? 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). |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting spaces
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 "GARY" wrote in message ... On Aug 17, 12:35 pm, MyVeryOwnSelf wrote: How can I delete all spaces after the last non-space character in each cell? 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). |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting spaces
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 On Aug 17, 7:59*pm, GARY wrote: How can I delete all spaces after the last non-space character in each cell? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting spaces
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 "Rick Rothstein (MVP - VB)" wrote in message ... 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 "GARY" wrote in message ... On Aug 17, 12:35 pm, MyVeryOwnSelf wrote: How can I delete all spaces after the last non-space character in each cell? 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting spaces in blank cells | Excel Discussion (Misc queries) | |||
Deleting unwanted lead spaces throughout a worksheet | Excel Worksheet Functions | |||
Deleting leading spaces in a cell | Excel Discussion (Misc queries) | |||
Deleting blank spaces in cells | Excel Discussion (Misc queries) | |||
Inserting/Deleting Spaces | Excel Worksheet Functions |