We’ve all been there; client’s data is rife with trailing or leading spaces and both TRIM() and CLEAN() have no effect. _If_ the data is well formed – each cell has 8 characters of real data – then you can use LEFT([cell],8) to peel off the trailing junk. _If_ the number of trailing (or leading) spaces is the same all the way down the column – each cell has 2 trailing non-printing characters immune to TRIM() and CLEAN() – you can use LEFT([cell],LEN([cell])-2) to rescue the valuable data.
What if the length of _BOTH_ the data and the garbage super-characters varies wildly? Try the following:
1) Insert three blank columns to the right of the data (herein referred to as columns B, C and D where the raw data is in A).
2) In column B use the following function (drag fill to the bottom of the data range);
a. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, CHAR(127), CHAR(32)), CHAR(129), CHAR(32)), CHAR(141), CHAR(32)), CHAR(143), CHAR(32)), CHAR(144), CHAR(32)), CHAR(157), CHAR(32)), CHAR(160), CHAR(32))
1. The extra spaces before the CHAR() functions is only used here to allow the email program to wrap the lines; do not use them in your formula.
2. The _ONLY_ modification to tailor this to another worksheet is to change the A2 to whatever is needed for the top of the data range.
3) In column C use the following function (drag fill to the bottom of the data range);
a. =TRIM(B2)
4) Copy column C into column D as values only.
a. Confirm the data is clean and well formed as needed.
5) Delete columns A – C.
Neither TRIM() nor CLEAN() will touch those seven CHARacters. The nested substitutes turn them all – no matter how many – into simple blank spaces (CHAR(32)). TRIM() is the function designed to purge superfluous blank spaces.
This solution works because there are only 7 characters TRIM() and CLEAN() ignore and they are documented in help.
I did not research this online, so I do not know if I re-invented the wheel but this wheel will get your client’s data where it needs to be, no matter how messed up the source data might be.
Bookmarks