VBA to Delete Leading ,Trailing and Excess Spaces in Excel

[ADITYAKANWAR]

Dear Experts

I am stuck in excel and need the help, on a daily basis we have received the Payment Details file from accounts dept. I am also attaching the file, it has lots of hidden spaces :crying:, which are not removed even using trim function, and find and replace procedure and we have to removed manually :banghead:

One of my friend using ASAP utilities, it's very fast and useful, but unfortunately, we can't download this software in my office due to security reasons
Is it possible to removed these hidden spaces


Thanks in Advance

Aditya Kanwar

[p45cal]

In the 2 cells in the sample file, the only nuisance characters are non-breaking spaces (ascii 160).
You can remove them with a formula:
=SUBSTITUTE(A1,CHAR(160),"")
copied down, then copy paste|Values into the original location.

Or you can use a short macro, select the cells first though:
Sub blah()
Selection.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

If there are perhaps other nuisance characters see TrimAll section here: http://dmcritchie.mvps.org/excel/join.htm#trimall

[ADITYAKANWAR]

Thanks a ton Sir,

both works like a charm