Processing Excel Whitespace

[yumbelie]

Hi, I've got a bit of a wierd problem, I have a spreadsheet which has ~100 unsorted records. I group these records according to a certain column, like filtering, only this procedure splits the records up onto seperate sheets automatically, so you get all with JAY in column C on one sheet, and MAY in column C on another. That works great, but I further format the new sheets with the names of the group they are under, e.g. Sheet 1's name becomes Group:JAY and sheet2' name becomes Group:MAY and so on. Problem is, some of the source records sometimes have trailing spaces, and this is where it gets a bit tricky, when the code sorts the records, those with trailing whitespace are sorted into different groups from those *with* trailing white space, e.g. (if the _ represents whitespace character) JAY goes in sheet 1, while JAY_ goes in group 2, JAY___ goes in group 3 etc. However, when I come to rename the sheets, I get an error saying 'duplicate name detected', as some part of VBA/EXcel ignores the excess white space. What puzzles me is that Trim(CellValue) does not remove this white space, I even wrote a function to try and do it, and it still doesn't get removed, whereas if I manaully sent either Trim() or my custom function a MsgBox(Trim("String &quot & "END&quot it will remove the white space. It's as if excels white space is treated differently, and it's horribly confusing me . Just for reference, if you enter the cell value, then press the space bar before going onto the next cell, thats where this 'specialcase' whitespace occurs. Anyway, users being what they are I want to know whats going on, and how I can eradicate this special whitespace.

Thanks All!

Joseph.

 

[combo]

I have tried

[tt]For Each c In Selection.Cells
c.Value = Trim(c.Value)
Next[/tt]

and all works fine. May be a problem arises due to referencing to wrong range, i.e. other workbook/worksheet.

 

[rmikesmith]

Joseph,

Send me a copy of your workbook, containing the raw records, and I'll be happy to take a look.

Email: rmikesmith@earthlink.net


Regards,
Mike

 

[xlbo]

try using CLEAN instead of TRIM. The whitespace could be linefeeds or carriage returns - Clean will get rid of 'em (usually ;-) ) Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[yumbelie]

Thanks guys, rmike: cheers for the offer, but I can't pass the book on else I'd get nailed over Data Protection. It's all healthcare associated stuff, so its got confidental written all over it. Thanks tho, I'll give CLEAN a go.

Joseph

 

[rmikesmith]

Joseph,

Know all about that situation... no problem.


Regards,
Mike


p.s. I try to learn something from Geoff every day.