CLEAN Function in Excel 2000 1

[Ben1637]

Hi Everybody:

I have a spreadsheet in Excel 2000 that has data imported from another system (maybe Oracle?) and I am trying to sort one of the columns. Although the data looks fine at quick glance, when I click on the individual cells, I can see up in the formula bar that many of the cells have an apostrophe before the numbers. This character is, obviously, affecting my abilities to sort.

I discovered the CLEAN function, but I have never used it before. Do you think this will solve my problem? If so, can you please help me generate a formula - the Help feature has been completely UNhelpful?!

My cell should read something like:
AL0051
But when I click in the formula bar, I see that it really says:
'AL0051

Can you please help me get rid of the ' ....I have a LOT of cells!

Any help and or suggestions you may have would be greatly appreciated.

Many thanks, in advance!

 

[bluedragon2]

If you want to get rid of all of them,:

find/replace find ' replace with nothing.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Ben1637]

I tried Find/Replace and didn't have any luck. Can you please help me with the syntax? Does it matter that the character doesn't display in the cell, but rather just up in the formula bar?

Thanks!!

 

[bluedragon2]

OK, this is an indicator that the data is text. Do this and post the result:

In another blank cell, put =LEN(<a cell that has problem>)



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[DaleWatson123321]

Here's a solution (tested and works)...

1) In a separate column, reference the first cell - e.g. =A1, and copy down for the number of rows.

2) Copy the column of formulas, and Paste Special - Values - onto itself - i.e. the same separate column.

3) Copy the separate column to the first column - e.g. column A.

4) Delete the separate column.

Hope this helps.

Regards, Dale Watson

 

[Ben1637]

When I type that formula into a new cell, it returns a number 6 (i.e. the number of real characters)

 

[bluedragon2]

Dale is right on for cleaning up the data, but, I do not see how it affects your sorting if all cells have the ' in front of them. Excel shouldn't even notice this on sorting, but if it does, and all cells have it, it should just go to the second character for sorting.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[DaleWatson123321]

Ben,

If you're still stuck, try my solution - but don't use the LEN function - only =A1 as explained in my previous posting.

Regards, Dale Watson

 

[Lilliabeth]

Typing an apostrophe in fromt of a number is a quick way to format the number into text.

Text sorts differently thatn numbers (everything that starts with a 1 at the top, then everything that starts with a 2, etc).

Multiply each entry by one and then do a copy and paste special (values) to return it to number format.

 

[Ben1637]

Dale, it worked!! Thanks so much! Your workaround was certainly much easier than trying to figure out a formula. THANK YOU VERY MUCH!!

Blue Dragon, you're right, if all of my cells had that apostrophe the sort would have worked, but unfortunately, the apostrophes were sporadic. Thanks for the suggestions, though!!

 

[b1kerch1ck]

Hey Guys,
I tried the above with Excel 2002 and the copy/paste values thing doesn't work.
However I did find a super groovy way to do the same thing, if anyone's interested.
Manually remove the apostrophe from one cell in the column. Copy and paste the format (using the paint brush button) to the rest of the column.
Voila! Bye-bye pesky apostrophes.