Thread: Excel help?

100I,

My guess is that the the values are actually text with space characters in there as well.

This is data exported from your system and then imported into Excel? What was the format? CSV?

Try using the clean, trim, right and left operators in an adjacent column to get rid of all the unwanted characters and then sort by the new column.

Cheers,

Mark

Thanks Mark,

I'm at home so not connected to the server to see what file type they actually are and cannot remember, but csv does ring a bell. I didn't build the report and my excel knowledge is limited but I can see it is set up to read *.asc;*.csv;*.tab;*.txt files. And yes AFAIK the data is retrieved from the original database (BTW based on a 20yr old platform I'm told) and stored for excel to read from, there are numerous reports then generated from these of course.
This newest report has a whole realm of data all on one big raw sheet so there is a world of statistics to be gleaned from it which is great, but for my stumbling block.

I've tried each of those operators you suggested, but no change. Am I right in assuming you mean you cannot see the extra characters at all, such as a 'space' that has been copied over from the original platform?
BTW I've done this as a new adjacent column for each, is this correct? Or can/should all four be done in one proceedure?

thanks again

cheers
Dan

Mark, I think you were on the right track, I've found that if I click the mouse anywhere on the formula bar then hit enter, the cell is now recognised by the filter. I've found the F2 hotkey to speed this up but it's still the slow way.
What exactly is this doing? Is there an operation I can select for the column to replicate this?

I've had this problem a lot of the years as I've done a lot of importing. No, I've never found a way to automate a conversion but I've usually found that "clean", etc fixes it.

When Excel has imported the data its done so as text, even though it looks like numbers. Editing the cell (clicking in it like you're doing) tells Excel to change the format from text to general. You could try selecting the column and changing the cell number format to general or % but I've found this seldom works.

When you imported the data, did you do it as a "delimited" file? If so, what separator did you use?