Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting SSNs
I'm having difficulty with a spreadsheet and socials - I need to format them
so the leading zeros remain and the hyphens go away. I was given a concate formula but in order to make that work I have to put it into the spreadsheet and I can't use the column where it goes. The parameters are assigned for uploading a flat file into our system. Can someone help me with low level directions. I'm literate but not that literate. Cheri |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting SSNs
If the SSNs are text fields:
'001-23-4567 then just do Edit Find and enter - In the Replace field enter nothing You should see: '001234567 -- Gary's Student "Cheri" wrote: I'm having difficulty with a spreadsheet and socials - I need to format them so the leading zeros remain and the hyphens go away. I was given a concate formula but in order to make that work I have to put it into the spreadsheet and I can't use the column where it goes. The parameters are assigned for uploading a flat file into our system. Can someone help me with low level directions. I'm literate but not that literate. Cheri |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting SSNs
I've tried that but when I copy and paste from the original spreadsheet into
my template, it reverts back to SSN format. The concat formula works great but I can't have it in my spreadsheet. I've tried the find/replace but it drops the zeros. "Gary''s Student" wrote: If the SSNs are text fields: '001-23-4567 then just do Edit Find and enter - In the Replace field enter nothing You should see: '001234567 -- Gary's Student "Cheri" wrote: I'm having difficulty with a spreadsheet and socials - I need to format them so the leading zeros remain and the hyphens go away. I was given a concate formula but in order to make that work I have to put it into the spreadsheet and I can't use the column where it goes. The parameters are assigned for uploading a flat file into our system. Can someone help me with low level directions. I'm literate but not that literate. Cheri |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting SSNs
After you replace the dashes with nothing, format the cells as Custom:
000000000 Select the cells Format | Cells | Number Click Custom and type 000000000 in the space provided. -- tj "Cheri" wrote: I've tried that but when I copy and paste from the original spreadsheet into my template, it reverts back to SSN format. The concat formula works great but I can't have it in my spreadsheet. I've tried the find/replace but it drops the zeros. "Gary''s Student" wrote: If the SSNs are text fields: '001-23-4567 then just do Edit Find and enter - In the Replace field enter nothing You should see: '001234567 -- Gary's Student "Cheri" wrote: I'm having difficulty with a spreadsheet and socials - I need to format them so the leading zeros remain and the hyphens go away. I was given a concate formula but in order to make that work I have to put it into the spreadsheet and I can't use the column where it goes. The parameters are assigned for uploading a flat file into our system. Can someone help me with low level directions. I'm literate but not that literate. Cheri |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting SSNs
Thank you - that works and your answer was easy to understand. Muchas Grass!
"tjtjjtjt" wrote: After you replace the dashes with nothing, format the cells as Custom: 000000000 Select the cells Format | Cells | Number Click Custom and type 000000000 in the space provided. -- tj "Cheri" wrote: I've tried that but when I copy and paste from the original spreadsheet into my template, it reverts back to SSN format. The concat formula works great but I can't have it in my spreadsheet. I've tried the find/replace but it drops the zeros. "Gary''s Student" wrote: If the SSNs are text fields: '001-23-4567 then just do Edit Find and enter - In the Replace field enter nothing You should see: '001234567 -- Gary's Student "Cheri" wrote: I'm having difficulty with a spreadsheet and socials - I need to format them so the leading zeros remain and the hyphens go away. I was given a concate formula but in order to make that work I have to put it into the spreadsheet and I can't use the column where it goes. The parameters are assigned for uploading a flat file into our system. Can someone help me with low level directions. I'm literate but not that literate. Cheri |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting SSNs
THANKS! I had read in the Excel Bible something like this but was unable to
make it work until your post! "Gary''s Student" wrote: If the SSNs are text fields: '001-23-4567 then just do Edit Find and enter - In the Replace field enter nothing You should see: '001234567 -- Gary's Student "Cheri" wrote: I'm having difficulty with a spreadsheet and socials - I need to format them so the leading zeros remain and the hyphens go away. I was given a concate formula but in order to make that work I have to put it into the spreadsheet and I can't use the column where it goes. The parameters are assigned for uploading a flat file into our system. Can someone help me with low level directions. I'm literate but not that literate. Cheri |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting SSNs | Excel Discussion (Misc queries) | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
Copy conditional formatting across multiple rows? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |