Some of my SSN's are being treated as formulas

[Gina K]

Hello,

I converted a spreadsheet from 123 to Excel and some of my SSN's are
evaluating as formulas. (When I look at the cell contents in the formula
bar, some cells have the equal sign preceeding the SSN.) Is there an easy
way to remove the equal sign?

Ideally, I want to store the numbers as 123456789, not 123-45-6789.

Thank you.

 

[Gary''s Student]

Edit > Find > =
replace with '

Just replace the = sign with an apostrophe.

 

[Rick Rothstein \(MVP - VB\)]

In addition to Gary's Student's post, you can use Find/Replace to remove the
minus signs too. Just put a minus sign in the Find field and leave the
Replace With field empty.

Rick

 

[Peo Sjoblom]

How about copy and paste special as values?

That will remove the equal sign, then just replace the hyphen with nothing


--


Regards,


Peo Sjoblom

 

[Dave Peterson]

I think replacing it with nothing (leaving it blank) would be better than that
apostrophe. Those leading apostrophes can be difficult to remove--well, if they
have to be removed later.

 

[iliace]

He said he wants them as numbers. So replace = with "", then set the
number format to "000-00-0000". It's under Format -> Cells -> Number -

 

[Dave Peterson]

And the OP said that excel is seeing the values as formulas. If Gina really
wanted to store the values as numbers, she'd have to remove the hyphens, too.

 

[Peo Sjoblom]

It doesn't make sense, why would anyone replace the equal sign in a formula
when all you have to do is to copy and paste special as values, then replace
the hyphen?


--


Regards,


Peo Sjoblom

 

[Dave Peterson]

If excel is seeing the data as a formula like:
=123-56-7890
then pasting as values would result in: -7823

And that's probably not a valid SSN <vbg>.

 

[Peo Sjoblom]

Ah! I assumed the OP had a text value. But you are right of course, she
said evaluating. Why not replacing the equal sign with nothing then?


--


Regards,


Peo Sjoblom

 

[Gary''s Student]

Hi Dave:

The only reason I suggested the apostrophe is that once the dashes are
removed, leading zeros will be preserved.

 

[Dave Peterson]

Excellent point. (Although you weren't the first to suggest that--vvbg!)

(It's tough to jump into the middle of a thread, huh <gd&r>.)

 

[Dave Peterson]

That makes sense, too. Personally, I like the numberformat approach, but it's
(obviously) a choice for the original poster.

My point was that getting rid of those leading apostrophes isn't as simple as
edit|replace. They can be a pain.