Forum Discussion
How to remove spaces at the end of a cell on a multi-row column
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?
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.
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?
Change the formula in L2 to
=IF(SUBSTITUTE(SUBSTITUTE(C2," ",""),CHAR(160),"")=SUBSTITUTE(SUBSTITUTE(D2," ",""),CHAR(160),""),"ja","nej")
See the attached version.