Getting rid of Tick Marks

gluemthkid

New Member
Joined
Sep 17, 2007
Messages
9
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
do a web search for ASAP utilities, it may just give you the power you need for this task
 
Upvote 0
What is the connection between the apostrophes (I assume that's what you mean?) and sorting by colour?
 
Upvote 0
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
 
Upvote 0
I take it then that your document is stored as TEXT, the item i listed above will work with that
 
Upvote 0
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.
 
Upvote 0
Use the Replace utility on the Excel Toolbar: Edit - Replace...
Find What [Box]: '
With What [Box]: Emply

This will remove all the marks!
 
Upvote 0
Try this: Select the column of tick-marked cells
Then Data | Text to Columns | Delimited | Next | Next | Choose General option.
Hope this helps! Larry
 
Upvote 0
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!!!!!!
 
Upvote 0
Try selecting the data, opening the VBEditor (Alt+f11) then open the Immediate Window (Ctrl+G) then type this in and press Enter:
Code:
selection.value = selection.value
Does that make a difference?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top