| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting SS#'s without hyphens
Is there any way to format a column of Social Security numbers without
hyphens? I've tried a custom format but that isn't working. Am I missing something? Thanks for your help. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatting SS#'s without hyphens
Anita
What do you have and what should a ss# look like. Format in Excel are not like placeholders in Access so you will likely need a temporary helper column while you add hyphens pb -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Anita" wrote in message ... Is there any way to format a column of Social Security numbers without hyphens? I've tried a custom format but that isn't working. Am I missing something? Thanks for your help. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatting SS#'s without hyphens
We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use
them to apply to a spreadsheet but without the hyphens i.e. 000000000. We'd like to format the entire column before importing to the spreadsheet. Thanks. I hope that's clearer. "Nick Hodge" wrote: Anita What do you have and what should a ss# look like. Format in Excel are not like placeholders in Access so you will likely need a temporary helper column while you add hyphens pb -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Anita" wrote in message ... Is there any way to format a column of Social Security numbers without hyphens? I've tried a custom format but that isn't working. Am I missing something? Thanks for your help. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatting SS#'s without hyphens
Anita
Not sure I do, but it sound like you want to remove the hyphens. If the SS# are in column A in B1 enter =TEXT(LEFT(A1,3)&MID(A1,5,2)&RIGHT(A1,4)) and copy down -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Anita" wrote in message ... We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use them to apply to a spreadsheet but without the hyphens i.e. 000000000. We'd like to format the entire column before importing to the spreadsheet. Thanks. I hope that's clearer. "Nick Hodge" wrote: Anita What do you have and what should a ss# look like. Format in Excel are not like placeholders in Access so you will likely need a temporary helper column while you add hyphens pb -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Anita" wrote in message ... Is there any way to format a column of Social Security numbers without hyphens? I've tried a custom format but that isn't working. Am I missing something? Thanks for your help. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatting SS#'s without hyphens
*After* importing, why not try:
<Edit <Replace And replace the dashes with nothing? -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Anita" wrote in message ... We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use them to apply to a spreadsheet but without the hyphens i.e. 000000000. We'd like to format the entire column before importing to the spreadsheet. Thanks. I hope that's clearer. "Nick Hodge" wrote: Anita What do you have and what should a ss# look like. Format in Excel are not like placeholders in Access so you will likely need a temporary helper column while you add hyphens pb -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Anita" wrote in message ... Is there any way to format a column of Social Security numbers without hyphens? I've tried a custom format but that isn't working. Am I missing something? Thanks for your help. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatting SS#'s without hyphens
Yes, I do want to remove the hyphens....thank you. I'll try that.
Anita "Nick Hodge" wrote: Anita Not sure I do, but it sound like you want to remove the hyphens. If the SS# are in column A in B1 enter =TEXT(LEFT(A1,3)&MID(A1,5,2)&RIGHT(A1,4)) and copy down -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Anita" wrote in message ... We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use them to apply to a spreadsheet but without the hyphens i.e. 000000000. We'd like to format the entire column before importing to the spreadsheet. Thanks. I hope that's clearer. "Nick Hodge" wrote: Anita What do you have and what should a ss# look like. Format in Excel are not like placeholders in Access so you will likely need a temporary helper column while you add hyphens pb -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Anita" wrote in message ... Is there any way to format a column of Social Security numbers without hyphens? I've tried a custom format but that isn't working. Am I missing something? Thanks for your help. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatting SS#'s without hyphens
Because I would have to do each number individually, wouldn't I? That's not
an option with as many as we may be bringing in. Thanks, RD "RagDyer" wrote: *After* importing, why not try: <Edit <Replace And replace the dashes with nothing? -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Anita" wrote in message ... We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use them to apply to a spreadsheet but without the hyphens i.e. 000000000. We'd like to format the entire column before importing to the spreadsheet. Thanks. I hope that's clearer. "Nick Hodge" wrote: Anita What do you have and what should a ss# look like. Format in Excel are not like placeholders in Access so you will likely need a temporary helper column while you add hyphens pb -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Anita" wrote in message ... Is there any way to format a column of Social Security numbers without hyphens? I've tried a custom format but that isn't working. Am I missing something? Thanks for your help. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatting SS#'s without hyphens
The Edit Replace can work on a range of cells <<all at once
Highlight or Select the range then use the Replace what: "-" Replace With: "" Click the "Replace All" Button HTH "Anita" wrote in message : Because I would have to do each number individually, wouldn't I? That's not an option with as many as we may be bringing in. Thanks, RD "RagDyer" wrote: *After* importing, why not try: <Edit <Replace And replace the dashes with nothing? -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Anita" wrote in message ... We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use them to apply to a spreadsheet but without the hyphens i.e. 000000000. We'd like to format the entire column before importing to the spreadsheet. Thanks. I hope that's clearer. "Nick Hodge" wrote: Anita What do you have and what should a ss# look like. Format in Excel are not like placeholders in Access so you will likely need a temporary helper column while you add hyphens pb -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Anita" wrote in message ... Is there any way to format a column of Social Security numbers without hyphens? I've tried a custom format but that isn't working. Am I missing something? Thanks for your help. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatting SS#'s without hyphens
Absolutely *NOT*!
You can "edit & replace" the *entire* sheet in one shot ... if you wish! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Anita" wrote in message ... Because I would have to do each number individually, wouldn't I? That's not an option with as many as we may be bringing in. Thanks, RD "RagDyer" wrote: *After* importing, why not try: <Edit <Replace And replace the dashes with nothing? -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Anita" wrote in message ... We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use them to apply to a spreadsheet but without the hyphens i.e. 000000000. We'd like to format the entire column before importing to the spreadsheet. Thanks. I hope that's clearer. "Nick Hodge" wrote: Anita What do you have and what should a ss# look like. Format in Excel are not like placeholders in Access so you will likely need a temporary helper column while you add hyphens pb -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Anita" wrote in message ... Is there any way to format a column of Social Security numbers without hyphens? I've tried a custom format but that isn't working. Am I missing something? Thanks for your help. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatting SS#'s without hyphens
JMay,
Great, thank you. That worked, however, SS#'s that start with zero eliminate the zero regardless of how I format the number. Any ideas? Anita "JMay" wrote: The Edit Replace can work on a range of cells <<all at once Highlight or Select the range then use the Replace what: "-" Replace With: "" Click the "Replace All" Button HTH "Anita" wrote in message : Because I would have to do each number individually, wouldn't I? That's not an option with as many as we may be bringing in. Thanks, RD "RagDyer" wrote: *After* importing, why not try: <Edit <Replace And replace the dashes with nothing? -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Anita" wrote in message ... We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use them to apply to a spreadsheet but without the hyphens i.e. 000000000. We'd like to format the entire column before importing to the spreadsheet. Thanks. I hope that's clearer. "Nick Hodge" wrote: Anita What do you have and what should a ss# look like. Format in Excel are not like placeholders in Access so you will likely need a temporary helper column while you add hyphens pb -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Anita" wrote in message ... Is there any way to format a column of Social Security numbers without hyphens? I've tried a custom format but that isn't working. Am I missing something? Thanks for your help. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatting SS#'s without hyphens
Preformat as Text, or prefix with an apostrophe, which will only be visible
in the formula bar, but invisible in the cell proper. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Anita" wrote in message ... JMay, Great, thank you. That worked, however, SS#'s that start with zero eliminate the zero regardless of how I format the number. Any ideas? Anita "JMay" wrote: The Edit Replace can work on a range of cells <<all at once Highlight or Select the range then use the Replace what: "-" Replace With: "" Click the "Replace All" Button HTH "Anita" wrote in message : Because I would have to do each number individually, wouldn't I? That's not an option with as many as we may be bringing in. Thanks, RD "RagDyer" wrote: *After* importing, why not try: <Edit <Replace And replace the dashes with nothing? -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Anita" wrote in message ... We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use them to apply to a spreadsheet but without the hyphens i.e. 000000000. We'd like to format the entire column before importing to the spreadsheet. Thanks. I hope that's clearer. "Nick Hodge" wrote: Anita What do you have and what should a ss# look like. Format in Excel are not like placeholders in Access so you will likely need a temporary helper column while you add hyphens pb -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Anita" wrote in message ... Is there any way to format a column of Social Security numbers without hyphens? I've tried a custom format but that isn't working. Am I missing something? Thanks for your help. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatting SS#'s without hyphens
I'm also having the same issue as Anita - but I don't understand the
directions you cite: =TEXT(LEFT(A1,3)&MID(A1,5,2)&RIGHT(A1,4)) Are you saying put 000-11-1234 in A1 and then this formula in B1? Is there another step after adding the formula you cite? "Nick Hodge" wrote: Anita Not sure I do, but it sound like you want to remove the hyphens. If the SS# are in column A in B1 enter =TEXT(LEFT(A1,3)&MID(A1,5,2)&RIGHT(A1,4)) and copy down -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Anita" wrote in message ... We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use them to apply to a spreadsheet but without the hyphens i.e. 000000000. We'd like to format the entire column before importing to the spreadsheet. Thanks. I hope that's clearer. "Nick Hodge" wrote: Anita What do you have and what should a ss# look like. Format in Excel are not like placeholders in Access so you will likely need a temporary helper column while you add hyphens pb -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Anita" wrote in message ... Is there any way to format a column of Social Security numbers without hyphens? I've tried a custom format but that isn't working. Am I missing something? Thanks for your help. |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatting SS#'s without hyphens
Again - same problem as Anita - - but how do you add the apostrophe to each
row if they all start with different numbers? Thanks, Joanne "RagDyer" wrote: Absolutely *NOT*! You can "edit & replace" the *entire* sheet in one shot ... if you wish! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Anita" wrote in message ... Because I would have to do each number individually, wouldn't I? That's not an option with as many as we may be bringing in. Thanks, RD "RagDyer" wrote: *After* importing, why not try: <Edit <Replace And replace the dashes with nothing? -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Anita" wrote in message ... We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use them to apply to a spreadsheet but without the hyphens i.e. 000000000. We'd like to format the entire column before importing to the spreadsheet. Thanks. I hope that's clearer. "Nick Hodge" wrote: Anita What do you have and what should a ss# look like. Format in Excel are not like placeholders in Access so you will likely need a temporary helper column while you add hyphens pb -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Anita" wrote in message ... Is there any way to format a column of Social Security numbers without hyphens? I've tried a custom format but that isn't working. Am I missing something? Thanks for your help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Deferring conditional formatting? | Excel Discussion (Misc queries) | |||
| Conditional Formatting: "handwritten" circles? | Excel Discussion (Misc queries) | |||
| Paste Link - retaining formatting | Excel Discussion (Misc queries) | |||
| conditional formatting with formula | Excel Worksheet Functions | |||
| Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
Linear Mode
