VBA Question

I got help in getting a data cleaning script written to help remove unwanted spaces and characters in my source data. It works pretty well but I have noticed that it has missed catching some blank spaces. Any idea on why?
Prouduction-IM-METRICS-T-V01.5.xlsm

VB ScriptMicrosoft ExcelVBA

Last Comment

Martin Liss

8/22/2022 - Mon

Simon

3/12/2015

They might be 'non-breaking spaces'. Just taking a look now at your sample file.
Please identify an example cell that has the problem.

To remove non-breaking spaces you could modify the "=TRIM" line in your CleanData macro

WS.Range("ZZ1:ZZ" & MaxRow).Formula = "=TRIM(SUBSTITUTE(" & Left(rCol.Address, InStr(1, rCol.Address, ":") - 1) & "1,CHAR(160),CHAR(32)))"

Open in new window

ASKER CERTIFIED SOLUTION

Martin Liss

3/12/2015

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Rrave26

3/13/2015

ASKER

I will try that today Martin, and get back to you with the results.

Rrave26

4/5/2015

ASKER

I agree. Martin. Thanks for the help.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Martin Liss

4/10/2015

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Certified Expert Program

Credly Partnership

Udemy Partnership