| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Some of my SSN's are being treated as formulas
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Some of my SSN's are being treated as formulas
Edit Find =
replace with ' Just replace the = sign with an apostrophe. -- Gary''s Student - gsnu200749 "Gina K" wrote: 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Some of my SSN's are being treated as formulas
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 "Gina K" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Some of my SSN's are being treated as formulas
How about copy and paste special as values?
That will remove the equal sign, then just replace the hyphen with nothing -- Regards, Peo Sjoblom "Gina K" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Some of my SSN's are being treated as formulas
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. Gary''s Student wrote: Edit Find = replace with ' Just replace the = sign with an apostrophe. -- Gary''s Student - gsnu200749 "Gina K" wrote: 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. -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Some of my SSN's are being treated as formulas
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 - Special. On Oct 11, 4:50 pm, Dave Peterson wrote: 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. Gary''s Student wrote: Edit Find = replace with ' Just replace the = sign with an apostrophe. -- Gary''s Student - gsnu200749 "Gina K" wrote: 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. -- Dave Peterson- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Some of my SSN's are being treated as formulas
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. iliace wrote: 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 - Special. On Oct 11, 4:50 pm, Dave Peterson wrote: 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. Gary''s Student wrote: Edit Find = replace with ' Just replace the = sign with an apostrophe. -- Gary''s Student - gsnu200749 "Gina K" wrote: 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. -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Some of my SSN's are being treated as formulas
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 "iliace" wrote in message ps.com... 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 - Special. On Oct 11, 4:50 pm, Dave Peterson wrote: 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. Gary''s Student wrote: Edit Find = replace with ' Just replace the = sign with an apostrophe. -- Gary''s Student - gsnu200749 "Gina K" wrote: 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. -- Dave Peterson- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Some of my SSN's are being treated as formulas
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 wrote: 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 "iliace" wrote in message ps.com... 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 - Special. On Oct 11, 4:50 pm, Dave Peterson wrote: 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. Gary''s Student wrote: Edit Find = replace with ' Just replace the = sign with an apostrophe. -- Gary''s Student - gsnu200749 "Gina K" wrote: 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. -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Some of my SSN's are being treated as formulas
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 "Dave Peterson" wrote in message ... 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 wrote: 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 "iliace" wrote in message ps.com... 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 - Special. On Oct 11, 4:50 pm, Dave Peterson wrote: 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. Gary''s Student wrote: Edit Find = replace with ' Just replace the = sign with an apostrophe. -- Gary''s Student - gsnu200749 "Gina K" wrote: 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. -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Some of my SSN's are being treated as formulas
Hi Dave:
The only reason I suggested the apostrophe is that once the dashes are removed, leading zeros will be preserved. -- Gary''s Student - gsnu200749 "Dave Peterson" wrote: 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. Gary''s Student wrote: Edit Find = replace with ' Just replace the = sign with an apostrophe. -- Gary''s Student - gsnu200749 "Gina K" wrote: 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. -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Some of my SSN's are being treated as formulas
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.) Peo Sjoblom wrote: 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 "Dave Peterson" wrote in message ... 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 wrote: 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 "iliace" wrote in message ps.com... 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 - Special. On Oct 11, 4:50 pm, Dave Peterson wrote: 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. Gary''s Student wrote: Edit Find = replace with ' Just replace the = sign with an apostrophe. -- Gary''s Student - gsnu200749 "Gina K" wrote: 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. -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Some of my SSN's are being treated as formulas
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. Gary''s Student wrote: Hi Dave: The only reason I suggested the apostrophe is that once the dashes are removed, leading zeros will be preserved. -- Gary''s Student - gsnu200749 "Dave Peterson" wrote: 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. Gary''s Student wrote: Edit Find = replace with ' Just replace the = sign with an apostrophe. -- Gary''s Student - gsnu200749 "Gina K" wrote: 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. -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Text treated as a numeric value? | Excel Discussion (Misc queries) | |||
| Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
| Formula Being treated as Text | Excel Worksheet Functions | |||
| Formula is treated as data | Excel Discussion (Misc queries) | |||
| Empty cell treated as 0 | Charts and Charting in Excel | |||
Linear Mode
