| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I change a social security number to a number series?
I want to take a spreadsheet that has numbers entered as social security
numbers and change the display to be a number series. Some of the numbers begin with 0. I'd like to have all the dashes removed as well. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I change a social security number to a number series?
So do you want to keep the 0's appended on the front? If so, use:
=TEXT(SUBSTITUTE(A1,"-",""),"000000000") If not (you want the 0's in front of the number to be taken out) use: =--SUBSTITUTE(A1,"-","") -- Regards, Dave "LCDawn" wrote: I want to take a spreadsheet that has numbers entered as social security numbers and change the display to be a number series. Some of the numbers begin with 0. I'd like to have all the dashes removed as well. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I change a social security number to a number series?
Let's say the numbers are text in the form:
'123-456-7890 Select the cells (columns or rows) and pull-down Edit Find and enter the - then replace with nothing The leading apostrophe will insure that leading zeros are retained -- Gary''s Student "LCDawn" wrote: I want to take a spreadsheet that has numbers entered as social security numbers and change the display to be a number series. Some of the numbers begin with 0. I'd like to have all the dashes removed as well. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I change a social security number to a number series?
On Thu, 22 Dec 2005 07:45:03 -0800, LCDawn
wrote: I want to take a spreadsheet that has numbers entered as social security numbers and change the display to be a number series. Some of the numbers begin with 0. I'd like to have all the dashes removed as well. Assuming the values are TEXT and not formatted numbers, then: =SUBSTITUTE(A1,"-","") will return the string, with the leading zeros. If you do not want the leading zeros, then =--SUBSTITUTE(A20,"-","") will convert the value into a number, and you can format it as you wish. You could also use FIND/REPLACE to replace the "-"'s with nothing. --ron |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I change a social security number to a number series?
You're Awesome, Dave...!
Thanks so much. Kindest Regards, Dawn "David Billigmeier" wrote: So do you want to keep the 0's appended on the front? If so, use: =TEXT(SUBSTITUTE(A1,"-",""),"000000000") If not (you want the 0's in front of the number to be taken out) use: =--SUBSTITUTE(A1,"-","") -- Regards, Dave "LCDawn" wrote: I want to take a spreadsheet that has numbers entered as social security numbers and change the display to be a number series. Some of the numbers begin with 0. I'd like to have all the dashes removed as well. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to change the color of all series in an excel chart in one go. | Charts and Charting in Excel | |||
| change info in other cells when i change a number in a drop list? | Excel Discussion (Misc queries) | |||
| Arrays Take too long. VERY HARD QUESTION. my head hurts : / | Excel Worksheet Functions | |||
| How to change the color of the series markers from yellow to Black | Charts and Charting in Excel | |||
| How to change the color of all series in an excel chart in one go. | Charts and Charting in Excel | |||
Linear Mode
