• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to apply CleanTrim UserDefine Function(UDF) through Evaluate on usedrange in one run without loop?

Dear All Respected Experts & Seekers,

I found 1 CleanTrim function created by Respected by Mr Rick Rothsteian on below link

Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
  Dim X As Long, CodesToClean As Variant
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
  If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
  For X = LBound(CodesToClean) To UBound(CodesToClean)
    If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
  CleanTrim = WorksheetFunction.Trim(S)
End Function
Above function work 1 cell in one time , but based on Evaluate method as per very knowledgeable reference from Chandoo.org

How can this (Sir Rick Rothstein's ) function apply on whole used range in one run without loop by help of Evaluate?

of course source link above from Excel fox have some successfully modified version available , but its run on single cell in one time with the help of loops.
but there are possible to run that in one run without loop through Evaluate but how?

Chandoo.org source ref for Evaluate
.Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(SUBSTITUTE(#,CHAR(160),""""))),"""")", "#", .Address))

how to modify below in our current requirement?
Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String

How to apply Evaluate approach ?
.Value = .Parent.Evaluate("CleanTrim("& activesheet.usedrange &"),True"))")
but not work.

Can some one can help regarding this? thank you in advance , help always be appreciated.


Chirag Raval
Last edited:

Marc L

Excel Ninja
Hi, I never tried with an UDF but your original function expects as first parameter a string​
so try with a string within your code instead of an object without any property …​
But I doubt it may work as it is : should replace the string parameter by a cell address and if only Evaluate works with an UDF.​

Marc L

Excel Ninja
It seems it can't work that way …​
For a range via VBA, instead of that kind of gas factory function it's better to use Excel inner features :​
Sub TrimClean(Rg As Range)
        Application.ScreenUpdating = False
    For Each V In Array(127, 129, 141, 143, 144, 157)
        Rg.Replace Chr$(V), "", xlPart
        Rg.Replace Chr$(160), " ", xlPart
        Rg.Value = Application.Trim(Application.Clean(Rg.Value))
        Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !​
Dear Sir @Marc L ,

Sorry for late reply,

Just Amazing, Directly cover all replaceable Chars in loop, & in another run on same range apply Clean Trim
Just Great & work like a charm.

As per this thread's requirement can it be convert as form of Evaluate ?
Can we convert your valuable code as like below
Dim myrng as srtring

usedrange=Evaluate(" & myrng & ", Application.trim(application.clean(" & myrng &", if#=mid{127, 129, 141, 143, 144, 157},replace("& myrng &", "#",""," & myrng & ")")
hope there are some way found..


Chirag Raval

Marc L

Excel Ninja
Evaluate works with a text as a formula, for example​
.Value = .Parent.Evaluate("IF({1},TRIM(CLEAN(SUBSTITUTE(" & .Address & ",CHAR(160),"" ""))))")
Dear Sir @Marc L ,

Thank you , it seem from , my mentioned reference of eliminate char 160 thread ...but how to apply this approach to our current thread?

Thank you very much.

Hope some way found there


Chirag Raval

Marc L

Excel Ninja
Let start from the beginning on Excel side, not on VBA aside :​
test a formula directly within a cell in order to get the clean & trim result of a source cell.​
Once you get a functional formula then - and only - it's time to think about Evaluate as a global VBA process.​
But maybe this process will run slower than my post #4 sample …​


Excel Ninja
Yep, in most cases Evaluate will be much slower than other methods.
Usually looping through multiple arrays (loaded from single column each) is fastest, then Formula/WorksheetFunction method, then comes Evaluate.

But in this case, since there is built in Range.Replace, it will likely be the fastest method.

Evaluate comes in handy, when speed isn't critical and when you want to process error values returned as well.
Dear Sirs @Chihiro & @Marc L ,

Thanks for spread light on the matter.
May be Loop on array seems better in this situation & then come netive functions but, Can we inherit extra charge to replace along with chr 160 in formula? Like replace array in formula {127,129,141,143,144,157,160} in one run?

actually I ( and may be other seekers) want to make this process as one liner as want to know & achieve how can we get similar result without loop.

Mysterious thing in evaluate , "Place Holder's " role like if #<>0 as it's say like every # as for each cell or that celll's value on which Evaluate method work at the same time but individually on every cell of course .at the same time . Like stormy could throwing lightening spark on every cell at same time & every spark is unique for that individual cell. This happens in same time on every cell, not one by one
In particular direction like loop do. That's seems must be achievable art or technique.

it will be great if we can get same result with Evaluate just want to know Evaluate can stand besides loop.

Hope there are still the place for Evaluate.
Though it's seems slower but stay with its core power of do the job on same time on every cell without loop.

in this thread , as Evaluate formula or function , also want more attention & deep understanding that require to know which function return what to process further on that return result.

Hope your some valuable attention regarding this thread's requirements.


Chirag Raval..
Last edited:

Marc L

Excel Ninja
The power of Evaluate method is its capacity to execute a formula array - not precised in its VBA help - but sometimes​
it may not understand such expectation so adding in the formula IF({1}, for example forces it running a formula array.​
If you need more characters to replace / delete, mod the original formula by adding SUBSTITUTE function for example.​
You can test for a single source cell directly within a cell formula ...​
Dear Sir @Marc L ,

Thank you for given your invaluable understanding on the matter.

Some where in this forum, you tips to Mr @YasserKhalil about same thing that " You must need on someday, to put {1} in Evaluate to force as formula array"

Now we are on core point on formula way
For Substitute multiple Char instead of just Chat(160) that's exciting to understand if we can do on more multiple iinstances nstead of work 1 instance at one time in one formula.

How can we understand core difference between Replace & Substitute?

If substitute work here then how to substitute multiple instances? How to force formula to accept Arrays of multiple instances need to replace in one string ?

Some time feel Evaluate is work like say physically force towards result by pressing Keyboard's Ctrl+Shift+Ent or As Ctrl+Enter
If you want result in one cell or multiple cells at one time That's feel great

We already done the job through loop way but there are another way to do the job that we need to understand

So how can I try substitute in formula to replace multi char in one string or on one cell?


Chirag Raval
Last edited:

Marc L

Excel Ninja
As we are on the VBA section, the better is to open a thread in the formula forum « Ask an Excel question »​
(just for a one cell formula) where some formula master - as I'm just a VBA ronin - may find a way …​
Dear Sir @Marc L ,

First i found this on search the web...


But, Shoking, Here is final answer? on same thread?

But... on here

IN "A1" x,da.”xyz”.c?e!

The following array formula removes those symbols


and affords the desired string shown below.

i try it but formula stuck on (“1:” can not resolve.

Another Find Here

i will try

Chirag Raval

Marc L

Excel Ninja
As I've no time to blow my mind with that kind of formula headache, ask to the formula masters …
Also I never had to delete / replace characters else than #0 to #31 & #160, so the original formula is enough on my side.
And in the case of more characters on huge range, I prefer to use an easy code to maintain like mine within post #4
'cause maybe a one line statement is a beauty but, if you don't understand it well, how can you maintain it ?!
Dear Sir @Marc L,

I already apply your invaluable VBA in rmy daily routine that do job like a Magic.
Many thanks for your efforts towards this thread.

Thanks Sir @Chihiro for your support.

Okay so I need to more search on formula way regarding Evaluate it & hope it will be found in this great forum.

May be I will be back soon..till than
Many many thanks to you all Experts.

Chirag Raval
Dear All,

Very interesting site found on Evaluate here

And Here

And Here
And Here

Hope some one can have the solution in mind.


Chirag Raval
Last edited:

Marc L

Excel Ninja
Nothing new within your links, you already had all the material within the samples from this forum !​
And again, restart from the beginning with the TEBV main rule : Think Excel Before VBA ‼​
So the trick is first to just create a valid formula which does the job for one result cell from one source cell, nothing else …​