Automatically deleting blank spaces in a cell

[Fuerteventura]

Is there a command which will delete blank spaces in a cell after the text has finished? for example, if the text in the cell is "Test Data ", I would like it to become "Test Data" (deleting the blank spaces to the right of the text), cheers.

 

[sbeatton]

If you download and use the freeware add-in ASAP utilities it has a function uder TEXT called delete leading and trailing spaces which should do the job.

www.asap-utilities.com/

 

[NBVC]

Use the TRIM function to delete abnormal spaces.

=Trim(A1)

 

[Fuerteventura]

Thank you both very much for your help - will experiment with your suggestions, cheers.

 

[Fuerteventura]

Use the TRIM function to delete abnormal spaces.

=Trim(A1)

Is there any way you can use a command with trim to apply it to the whole worksheet, rather than recording a macro which adds a column with the trim, copying PasteSpecial-values, for each column, cheers.

 

[NBVC]

Check out Banker1's macro solution in this thread:

http://www.mrexcel.com/board2/viewtopic.php?t=179017&highlight=

Is that what you need.

 

[Fuerteventura]

Yep that's perfect - many thanks for the link. Here is the code for anyone looking this up in a search:

Sub TrimCells()

Dim WorkRange As Range

Set WorkRange = Range("A1:I65536")

For Each Cell In WorkRange
    If Cell.Value = "" Then
        GoTo Next1
    Else
        Cell.Value = Trim(Cell.Value)
    End If
Next1:
Next Cell


End Sub