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.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,275
Office Version
2019, 2016, 2013
Platform
Windows
do a web search for ASAP utilities, it may just give you the power you need for this task
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,793
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
What is the connection between the apostrophes (I assume that's what you mean?) and sorting by colour?
 

gluemthkid

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

Moderator
Joined
Oct 23, 2004
Messages
10,275
Office Version
2019, 2016, 2013
Platform
Windows
I take it then that your document is stored as TEXT, the item i listed above will work with that
 

gluemthkid

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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Use the Replace utility on the Excel Toolbar: Edit - Replace...
Find What [Box]: '
With What [Box]: Emply

This will remove all the marks!
 

indiantrix

Well-known Member
Joined
Oct 17, 2002
Messages
943
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

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,793
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,201
Messages
5,443,002
Members
405,212
Latest member
Arnie58

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top