RichWalk

Hi Chaps, I am wk'ing on a large spreadsheet and all the data has an apostophe in front of it eg 'SEB100, The help file suggests this is a navigation content from Lotus 123 and tells u to uncheck the navigation key box in Tools> Options. I have tried this & nothing has changed. Using Microsoft Office 2000 on WIN98SE.

Any help would be much appreciated

Cheers

Rich

mj

I would try:

CTRL+A to select all cells in the workbook

edit/find, the find and replace dialogue will appear. click on the replace tab.

in the find field put '
leave he replace field blank.


click on "replace all" , this should dlete all the 's


I have just done this with office XP, I imagine yours is similar, strangeley enough I could not get an ' into any cells. I could put it in, but when I clicked off the cell it dissapeared - this may mean that what I have written above is complete tosh, in any event I would make a backup of the file.

DJ Dunk

iTrader: (5)

The apostrophe in Excel is a text qualifier. Any text that follows goes into the cell as text but you don't see the apostrophe. You should be able to remove them with the 'replace' function.

[Edited by DJ Dunk - 9/19/2003 4:12:23 PM]

mj

that would explain why it dissapears then

RichWalk

hi, I have tried as suggested but keeps telling me Excel cant find any qualifying cells with the ' in to replace.

This is doing my head in now...

mj

it worked for me anyway - try selecting a few cells at a time.

Buckrogers

I cant seem to replicate the problem.

If you have one ' at the start of the data entered in a single cell;
a) It shows in the formula bar, but is not displayed in the cell itself.
b) Try doing a search for ' on the cell, excel cant find it.

If you have '' at the start of the data entered in a single cell;
a) It shows '' in the formula bar and one ' in the cell itself.
b) Find command is able to find it.

Does not appear to be anything in the tools options menu to change the displayed cell contents.

It could be the formatting on the cell.
Right click on a cell affected, format cells, click number tab, select general and ok. What does that do?

It might be that the cells have some custom setting to them, which would not appear in the formula bar but would be displayed in the cell. This would also explain why the find command was not working.

Failing that, you could try:
Find another workbook, copy the format of another cell and apply that format to one of these affected cells.

If that does not work, then copy a small selection of the affected data into a new workbook and email it to me.