Replace, Clean & Trim VBA

Question

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!

Answer 1

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))