Remove ' character from copied excel cell to match data

[Syndrome]

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.

 

[Ron Rosenfeld]

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

 

[Toppers]

If you want to change it to numeric:

Put 1 in an empty cell
Copy this cell
Highlight data to be changed
Edit==>Paste Special==>Operation-->Multiply
delete 1 fom your "helper" cell

HTH

 

[David Biddulph]

The apostrophe will set the contents of the cell to text. If you want to
convert it to a number, try using paste special to add zero or multiply by
1.

 

[James Silverton]

David wrote on Wed, 7 Mar 2007 12:37:41 -0000:

DB> The apostrophe will set the contents of the cell to text.
DB> If you want to convert it to a number, try using paste
DB> special to add zero or multiply by 1.
DB> --
DB> David Biddulph

DB> message

??>> 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

??>> Hope you can help.

Can you tell me if I am doing something wrong with this problem?
I am using Office 2002 and, if I select the column of cells with
text representations, I get the signal "number stored as text"
and clicking "convert to number" seems to be make the necessary
change. This works even if some of the cells are really numbers.
I have had similar problems to the OP with inherited data or
that entered by others.


James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

 

[Dave Peterson]

What you're doing is fine--if you have a small quantity of values that need to
be fixed. But if you had 64k or 1mb of data that needed to be fixed, you may
want to do it a different way.

 

[James Silverton]

What you're doing is fine--if you have a small quantity of
values that need to
be fixed. But if you had 64k or 1mb of data that needed to be
fixed, you may
want to do it a different way.

Oh, I see. The conversion process does take an appreciable
amount of time, tho' even an array of 50 000 text numbers took
under a minute. The paste special route has the advantage of
being faster, I guess.

 

[Dave Peterson]

Ahhh. I thought you were doing them one by one.

The only problem I had was when I selected a range (or column), I had to make
sure that the activecell in that selection had the problem.

After I did that, it was very quick.

 

[James Silverton]

Dave wrote on Wed, 07 Mar 2007 09:28:34 -0600:

DP> The only problem I had was when I selected a range (or
DP> column), I had to make sure that the activecell in that
DP> selection had the problem.

DP> After I did that, it was very quick.

??>> Oh, I see. The conversion process does take an
appreciable
??>> amount of time, tho' even an array of 50 000 text numbers
??>> took under a minute. The paste special route has the
??>> advantage of being faster, I guess.

Yes, I discovered that too on an occasion where the first number
was actually a real one! Selecting an array can be a bit tricky
but it can be done.

Thanks again for the discussion!

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

 

[Dave Peterson]

I like this...it's much more straight forward to explain--but the users have to
be using xl2002+ and have that error checking option turned on.