Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove varying amounts of space characters
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove varying amounts of space characters
Try the "CLEAN" Function - it will remove all "NON-PRINTING" characters, (i.e. tabs, spaces, etc.). -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640 View this thread: http://www.excelforum.com/showthread...hreadid=500976 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove varying amounts of space characters
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove varying amounts of space characters
Thank you Ken. This works great
"Ken Johnson" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove varying amounts of space characters
Hi Joe,
Great! Thanks for the feedback. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
REMOVE SPECIAL CHARACTERS FROM TEXT CELLS | Excel Worksheet Functions | |||
Remove 1st 3 characters in each field of Excel column | Excel Worksheet Functions | |||
Remove single text characters | Excel Discussion (Misc queries) |