| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add a 0 and 2 dashes to a truncated soc. sec.# in a cell
I have a spreadsheet that has social security numbers (SSN) in a column. The
numbers are missing the leading zeros and the two dashes for the SSN format (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell, the you see only 8 numbers, but when you format you of course see the SSN format...000-00-0000. I need to add the leading zero and then the two dashes (this because I am transferring the numbers to another software package). My data set is large..manual entry will take forever. Any ideas are welcome. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I add a 0 and 2 dashes to a truncated soc. sec.# in a cell
Add this in another column
=TEXT(A1,"0000-000-0000") copy down and use this data -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allan" wrote in message ... I have a spreadsheet that has social security numbers (SSN) in a column. The numbers are missing the leading zeros and the two dashes for the SSN format (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell, the you see only 8 numbers, but when you format you of course see the SSN format...000-00-0000. I need to add the leading zero and then the two dashes (this because I am transferring the numbers to another software package). My data set is large..manual entry will take forever. Any ideas are welcome. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I add a 0 and 2 dashes to a truncated soc. sec.# in a cell
Hi Allan,
try ="0"&left(a2,2)&"-"&mid(a2,3,2)&"-"&right(a2,4) hth regards from Brazil Marcelo "Allan" escreveu: I have a spreadsheet that has social security numbers (SSN) in a column. The numbers are missing the leading zeros and the two dashes for the SSN format (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell, the you see only 8 numbers, but when you format you of course see the SSN format...000-00-0000. I need to add the leading zero and then the two dashes (this because I am transferring the numbers to another software package). My data set is large..manual entry will take forever. Any ideas are welcome. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I add a 0 and 2 dashes to a truncated soc. sec.# in a cell
After a bit of experimentaion this seemed to do the job.
=CONCATENATE("0",LEFT(A1,2),"-",MID(A1,3,2),"-",RIGHT(A1,4)) "Allan" wrote: I have a spreadsheet that has social security numbers (SSN) in a column. The numbers are missing the leading zeros and the two dashes for the SSN format (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell, the you see only 8 numbers, but when you format you of course see the SSN format...000-00-0000. I need to add the leading zero and then the two dashes (this because I am transferring the numbers to another software package). My data set is large..manual entry will take forever. Any ideas are welcome. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I add a 0 and 2 dashes to a truncated soc. sec.# in a cell
You may want to try this Sub add_zero_dash() const_dash = "-" 'assuming data is in column A rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row For i = 1 To rowcount Range("a" & i).Select entirename = ActiveCell.Value check_len = Len(entirename) If check_len = 8 Then new_val = "0" & entirename Selection.NumberFormat = "@" ActiveCell.Value = new_val new_val_first = Left(new_val, 3) new_val_last = Right(new_val, 3) new_val_mid = Mid(new_val, 4, 3) ActiveCell.Value = new_val_first & const_dash & new_val_mid & const_dash & new_val_last End If Next End Sub -- jetted ------------------------------------------------------------------------ jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532 View this thread: http://www.excelforum.com/showthread...hreadid=559431 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I add a 0 and 2 dashes to a truncated soc. sec.# in a c
Thanks...you gave me the seed (and possibly the fruit) of my solution. You
guys are good! "Marcelo" wrote: Hi Allan, try ="0"&left(a2,2)&"-"&mid(a2,3,2)&"-"&right(a2,4) hth regards from Brazil Marcelo "Allan" escreveu: I have a spreadsheet that has social security numbers (SSN) in a column. The numbers are missing the leading zeros and the two dashes for the SSN format (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell, the you see only 8 numbers, but when you format you of course see the SSN format...000-00-0000. I need to add the leading zero and then the two dashes (this because I am transferring the numbers to another software package). My data set is large..manual entry will take forever. Any ideas are welcome. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I add a 0 and 2 dashes to a truncated soc. sec.# in a c
Timm...I will experiment with your solution too...you guys are good. Thanks "tim m" wrote: After a bit of experimentaion this seemed to do the job. =CONCATENATE("0",LEFT(A1,2),"-",MID(A1,3,2),"-",RIGHT(A1,4)) "Allan" wrote: I have a spreadsheet that has social security numbers (SSN) in a column. The numbers are missing the leading zeros and the two dashes for the SSN format (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell, the you see only 8 numbers, but when you format you of course see the SSN format...000-00-0000. I need to add the leading zero and then the two dashes (this because I am transferring the numbers to another software package). My data set is large..manual entry will take forever. Any ideas are welcome. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I add a 0 and 2 dashes to a truncated soc. sec.# in a c
Marcelo...your solution is excellent!...Thanks a bunch...I would love to keep
in touch. "Marcelo" wrote: Hi Allan, try ="0"&left(a2,2)&"-"&mid(a2,3,2)&"-"&right(a2,4) hth regards from Brazil Marcelo "Allan" escreveu: I have a spreadsheet that has social security numbers (SSN) in a column. The numbers are missing the leading zeros and the two dashes for the SSN format (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell, the you see only 8 numbers, but when you format you of course see the SSN format...000-00-0000. I need to add the leading zero and then the two dashes (this because I am transferring the numbers to another software package). My data set is large..manual entry will take forever. Any ideas are welcome. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I add a 0 and 2 dashes to a truncated soc. sec.# in a c
thanks for the feedback.
regards Marcelo "Allan" escreveu: Marcelo...your solution is excellent!...Thanks a bunch...I would love to keep in touch. "Marcelo" wrote: Hi Allan, try ="0"&left(a2,2)&"-"&mid(a2,3,2)&"-"&right(a2,4) hth regards from Brazil Marcelo "Allan" escreveu: I have a spreadsheet that has social security numbers (SSN) in a column. The numbers are missing the leading zeros and the two dashes for the SSN format (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell, the you see only 8 numbers, but when you format you of course see the SSN format...000-00-0000. I need to add the leading zero and then the two dashes (this because I am transferring the numbers to another software package). My data set is large..manual entry will take forever. Any ideas are welcome. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I add a 0 and 2 dashes to a truncated soc. sec.# in a c
This is far easier than my frankenfunction solution. :O)
I often find better ways to do things on this forum. "Bob Phillips" wrote: Add this in another column =TEXT(A1,"0000-000-0000") copy down and use this data -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allan" wrote in message ... I have a spreadsheet that has social security numbers (SSN) in a column. The numbers are missing the leading zeros and the two dashes for the SSN format (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell, the you see only 8 numbers, but when you format you of course see the SSN format...000-00-0000. I need to add the leading zero and then the two dashes (this because I am transferring the numbers to another software package). My data set is large..manual entry will take forever. Any ideas are welcome. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I add a 0 and 2 dashes to a truncated soc. sec.# in a c
Have you tried formatting the cells as SSN?
If your column of numbers are true numbers, Select the column, then, <Format <Cells <Number tab, Click on "Special" in the left window, Then click on "Social Security Number" in the right window. This will *add* leading 0's and dashes. If your numbers are 'Text', Right click in a new, empty, unused cell, and choose "Copy", Select the column of numbers and right click in the selection, Choose "Paste Special", Then click on "Add", then <OK. You NOW have true numbers, so you can now format as above, to SSN. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Allan" wrote in message ... Timm...I will experiment with your solution too...you guys are good. Thanks "tim m" wrote: After a bit of experimentaion this seemed to do the job. =CONCATENATE("0",LEFT(A1,2),"-",MID(A1,3,2),"-",RIGHT(A1,4)) "Allan" wrote: I have a spreadsheet that has social security numbers (SSN) in a column. The numbers are missing the leading zeros and the two dashes for the SSN format (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell, the you see only 8 numbers, but when you format you of course see the SSN format...000-00-0000. I need to add the leading zero and then the two dashes (this because I am transferring the numbers to another software package). My data set is large..manual entry will take forever. Any ideas are welcome. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| insert date | Excel Worksheet Functions | |||
| Cell Format & Dashes | Excel Discussion (Misc queries) | |||
| Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
| Possible Lookup Table | Excel Worksheet Functions | |||
| Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Linear Mode
