Forum Discussion
How to remove spaces at the end of a cell on a multi-row column
| 07:30 - 15:30 (00) |
| 15:30 - 00:00 (00) |
| 00:00 - 07:45 (210) |
| 00:00 - 07:30 (210) |
| 00:00 - 07:30 (00) |
| 22:45 - 07:15 (00) |
| 07:30 - 22:00 (00) |
| 06:00 - 06:34 (00) |
| 07:15 - 07:45 (00) |
| 07:45 - 00:00 (15) |
| 06:30 - 07:30 (00) |
| 07:30 - 22:30 (00) |
| 22:00 - 08:00 (00) |
| 14:00 - 22:00 (00) |
| 07:30 - 15:30 (00) |
| 20:00 - 00:00 (00) |
| 00:00 - 07:00 (105) |
Thank you Hans
I think it is another problem. I will use the IF funktion, but it dosn´t work. I have som output from another program (Lessor Workforce) and I would like to compare thise to colums, I kan see in column C there is different gaps. If I change it, the IF function is still not working. I need a sheet who tells me if there are different hours in columns C and D. Can You see what the problem is?
6 Replies
Here is a macro. Select the range before running it.
Sub RemoveSpaces() Dim c As Range Application.ScreenUpdating = False For Each c In Selection c.Value = Trim(Replace(c.Value, Chr(160), "")) Next c Application.ScreenUpdating = True End SubSelect the range.
Press Alt+F11 to activate the Visual Basic Editor.
Select Insert > Module.
Copy the code into the new module.
Place the insertion point anywhere in the code.
Press F5 to run it.
Switch back to Excel.
If this was a one-off, you can save the workbook as it is.
If you want to keep the macro for future use, save the workbook as a macro-enabled workbook (.xlsm)
If you find this too complicated:
Let's say your data are in A1:A100.
Enter the following formula in B1:
=TRIM(SUBSTITUTE(A1,CHAR(160),""))
Fill down to B100.
Column B will now contain the strings without trailing spaces.
If you wish, you can copy column B and paste as values, to remove the formulas.
