Getting rid of Tick Marks

[gluemthkid]

Ok - here's the deal. Some sent me a large spreadsheet with random rows throughout the spreadsheet highlighted in diffierent colors. I have a module that will sort the spread sheet by Color - however it doesn't work on this spreadsheet because - for whatever reason - every cell starts with a tick mark.

I tried to do a replace all - and excel just told me I was crazy.

I even tried going through and manually removing the tick marks - still no luck.

If I export the sheet in to txt and then re-import it I will loose all the highlights...

Any suggestions.

 

[mole999]

do a web search for ASAP utilities, it may just give you the power you need for this task

 

[RoryA]

What is the connection between the apostrophes (I assume that's what you mean?) and sorting by colour?

 

[gluemthkid]

I DONT KNOW!!!

For whatever reason - this formula/module that I have that will sort by color - works on every other spreadsheet EXCEPT for this one - with these pescky littel tick marks - and yes I am referring to Apostrophes

 

[mole999]

I take it then that your document is stored as TEXT, the item i listed above will work with that

 

[gluemthkid]

It's an excel doc. I didn't make it. Someone else sent it to me this way.

When I try to format the cells it doesn't say that they are store as text.

They aren't numbers anyway.

REGARDLESS - i found a solution to my first issue which was sorting the sheet by color.

I have EXCEL 2002(xp) on my machine.... I found someone with EXCEL 2007 and it has the sort by color feature built in - so all of this was a mute point. - If I had 07 that is....

Anyway I had him open the spreadsheet - sort it as I needed it and saved it back as a 97-2003 .xls file.

Still would be nice to know how to get rid of those tick marks - that apparently aren't recognizable as anything except annoying.

Thanks for your suggestions.

 

[Joe Was]

Use the Replace utility on the Excel Toolbar: Edit - Replace...
Find What [Box]: '
With What [Box]: Emply

This will remove all the marks!

 

[indiantrix]

Try this: Select the column of tick-marked cells
Then Data | Text to Columns | Delimited | Next | Next | Choose General option.
Hope this helps! Larry

 

[gluemthkid]

Something tells me that there is just something WACKY about how this person made this spreadsheet.

Nothing works.

All of your suggestions are things that when I try it on a different spreadsheet - it works but not this one.....

The only thing that helped was having someone else with 2007 open it and do the sort by color for me - but those D@RN tick marks are still there.

Even If I do a Ctrl+H to replace them - no dice it says: Microsoft Excel cannot find any data to replace.

If I put a tick mark in the middle of a cell - No problem it will replace it fine and dandy -

It's those tick marks at the start of the cell.

They dont show up until you click on the cell and look up in the formula bar.....

THEY ARE DRIVING ME CRAZY!!!!!!

 

[RoryA]

Try selecting the data, opening the VBEditor (Alt+f11) then open the Immediate Window (Ctrl+G) then type this in and press Enter:

selection.value = selection.value

Does that make a difference?