Removing spaces within cells but only at the end of the data string

[SteveNL86]

Dear reader,

I'm dealing with a workbook imported from a SQL database but I've noticed that sometimes there is an extra space within cells.
So I will have a 'Smith, J. ' instead of just 'Smith, J.'. This is breaking some of the formula's I'm using.

The sheet is rather large, is there a quick way I can remove spaces within all the cells but only if they occur at the end of the data string?

Any help is much appreciated!

Kind regards,
Steve

 

[Fluff]

Is it a normal space or some other character?
What do you get if you use

=code(right(A2))

change A2 to references one of the cells with an extra space

 

[SteveNL86]

It's just a normal space.

I get this:

46​

46​

32​

110​

32​

110​

32​

32​

32​

110​

101​

32​

32​

32​

32​

32​

32​

32​

32​

 

[Fluff]

Ok, how about

Sub SteveNL()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate("trim(" & .Address & ")")
   End With
End Sub

 

[SteveNL86]

Thanks Fluff, seems to have done the trick!

Thanks for the help!

 

[Fluff]

You're welcome & thanks for the feedback.