On Wed, 7 Mar 2007 03:51:13 -0800, Syndrome
wrote:

I have copied/paste special data from one spreadsheet to another in order to
compare and match the data on this second spreadsheet.

The data I have copied appears like this in the cell - 123456
but in the formula bar appears like this - '123456

I want to match data from the other sheet where the cell entry is - 123456
and is like this in both cell and formula bar.

I have six hundred rows of data to match using an IF formula but as the the
data in the formula bar is different the data won't match. Is there anyway of
removing the ' character prior to the numerical data in the copied cell
without having to enter each cell and remove it. I've tried a find & replace
search but that won't recognise the ' character within the copied cell.

Hope you can help.


The ' character is indicating that the data in the cell is being treated as
TEXT and not as a number.

One method of converting the data to a number:

1. Be sure you have a backup of your data, or can easily reproduce the
worksheet in case something goes wrong.

2. Select a blank cell on your worksheet.
3. Edit/Copy
4. Select the cells where your data appears.
5. Edit/Paste Special/Add


--ron