Remove spaces automatically.

[James bolton]

Hi,

I have created a spreadsheet tool, where basically someone copy and paste's a page from our system, and it automatically populates an email based on the information from that pasted page.

The problem is, some cells have a lot of spaces after the word. Say one of the cells picks up a figure, it will have around 20 spaces after the number, and it comes out on the email.

For example, the email will populate like this.

The employee JOE BLOGGS has made a total of 20 sales for a total of £ 30 and $50.

It's really annoying. Is there a macro that ensures that in a cell, there are no more than 1 space between each word ?

 

[Deepak]

Sample file pls. Meanwhile you may play with TRIM,REPLACE too.

 

[Gary's Student]

Give this a try:

Sub SpaceKiller()
  Dim r As Range, wf As WorksheetFunction
  Set wf = Application.WorksheetFunction
 
  For Each r In Cells.SpecialCells(xlCellTypeConstants)
    With r
      .Value = wf.Trim(.Value)
    End With
  Next r
End Sub

 

[Alex24]

Hi,

You can try and adapt this to your needs. It can be easily incorporated in any code.


Sheets("type in sheet name").Columns("type column letter").Select
For Each cell In Selection
cell.Value = Application.WorksheetFunction.trim(cell.Value)
Next

Cheers!

 

[Marc L]

Hi !

No need a loop neither Select (bad !) with Excel Trim function :
just directly apply on cells range !
​

 

[shaikhrulez]

Try this formula

=TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))

It removes all the extra annoying space & leave only one space between each word. I hope this will help.