Remove varying amounts of space characters

G

Guest

Hi all,

I'm hoping someone out there can help. I've been working with this Excel
file that I believe was imported from an external app. Column A has a bunch
of data along with a whole slew of empty cells. Problem is, they are not
empty. I copied and pasted the column into Word so I could see the invisible
characters, and low and behold, they were many. Some cells had space & tab
characters after existing text, others had space characters in the blank
cells. Additionally, the spaces were not all equal. Sometimes it was 15
spaces, sometimes 8, etc. I need to physically remove all these invisible
characters so sorting & filtering will work the way it's supposed to.

Reading through the available threads, I couldn't find anything for my
situation. I tried FInd/Replace/ALT 0160 and that didn't work because it
only accounts for one single space. Can't use TRIM since the spaces are
varying in lengths. Does anyone have any suggestions?

Thanks,
Joe
 
W

wjohnson

Try the "CLEAN" Function - it will remove all "NON-PRINTING" characters,
(i.e. tabs, spaces, etc.).
 
K

Ken Johnson

CLEAN doesn't remove spaces so you will also have to use TRIM which
removes all spaces except for single spaces that are between other
characters.
Ken Johnson
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Re: Formula to Remove Special Characters 5
How do I remove extra space when text wrapping? 2
How do I remove spaces at the beginning of cells? 3
How do you remove trailing spaces withour Trim function? 8
Substituting for special characters 4
Macro to eliminate extra space 3
Remove white spaces in Back 2
delete empty space after certain characters 4

Top