CODEWITHSUNDEEP
excelvba
Error 1004
Error 1004

Reputation: 8220

Replace, Clean & Trim VBA

I m trying to:

  1. Replace double spaces with single.
  2. Clean
  3. Trim

the same string and i use:

AmountI = Replace(Application.WorksheetFunction.Clean(Trim(.Cells(j, 9).Value)), "  ", " ")

I want to know if there is a specific order of those three VBA functions or the order does not play any role in the outcome.thanks for the help!

Upvotes: 3

Views: 4076

Answers (2)

user4039065
user4039065

Reputation:

vba.trim doesn't remove double spacing, only leading/trailing spaces. You need the worksheet version Application.Trim or WorksheetFunction.Trim to remove interim double spaces.

If you have triple spacing, a single Replace will leave you with a double space. Application.Trim will not.

You don't want Clean to process characters (i.e. spaces) that you are going to remove anyways so Trim first.

.Value2 (without currency or date information) is marginally faster processing when you don't have currency or date data; you have a string so use .Value2.

AmountI = Application.Clean(Application.Trim(.Cells(j, 9).Value2))

Upvotes: 5

Harassed Dad
Harassed Dad

Reputation: 4704

 AmountI = Trim(Replace(Application.WorksheetFunction.Clean(.Cells(j, 9).text), "  ", " "))

My logic is: 1 .Text is faster than .Value 2 Clean may close up two spaces by removing a non-printing char between them, so do this before... 3 Turn double spaces to single with replace, then 4 Remove leading and trailing spaces

Upvotes: 0

Related Questions