Remove formatting from SSN

I exported some data from an Access database.  The Social Security Numbers 
are in this format:  123-45-6789.  I would like to change them all to 
123456789.

I've tried creating a Custom Format and formatting the cells, but nothing 
changed.  Tried setting the format of a new column, then cutting and pasting 
or Paste Special into the new cells, but it keeps the old formatting.  Is 
there an answer? 


0
froufle1 (6)
10/7/2005 9:38:53 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
1310 Views

Similar Articles

[PageSpeed] 24

Try this in a "helper" column:

=TEXT(A1,"000000000")

To retain your leading zeroes.
-- 
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Claire View" <froufle@DELETETHIShotmail.com> wrote in message
news:%23qn3Ud4yFHA.2008@TK2MSFTNGP10.phx.gbl...
> I exported some data from an Access database.  The Social Security Numbers
> are in this format:  123-45-6789.  I would like to change them all to
> 123456789.
>
> I've tried creating a Custom Format and formatting the cells, but nothing
> changed.  Tried setting the format of a new column, then cutting and
pasting
> or Paste Special into the new cells, but it keeps the old formatting.  Is
> there an answer?
>
>

0
ragdyer1 (4060)
10/7/2005 10:03:58 PM
Select the column and 
edit|replace
what: - (dash)
with: (leave blank)
replace all

Then apply your custom format of 000000000

Or use a helper column of cells filled with formulas like:

=text(--substitute(a1,"-",""),"000000000")



Claire View wrote:
> 
> I exported some data from an Access database.  The Social Security Numbers
> are in this format:  123-45-6789.  I would like to change them all to
> 123456789.
> 
> I've tried creating a Custom Format and formatting the cells, but nothing
> changed.  Tried setting the format of a new column, then cutting and pasting
> or Paste Special into the new cells, but it keeps the old formatting.  Is
> there an answer?

-- 

Dave Peterson
0
petersod (12005)
10/8/2005 1:06:25 AM
Hi Claire,
I can't imagine why you would want to change them from text to
numbers, but since you can't change the number format and have it
take right away you have text.   You can fix by using  Ctrl+H and
replacing the "-"   with nothing.     It will be text and you want numbers
so you will then have do something like add  and empty cell to each.

Select and copy an empty cell
Select the column of ssno then   Edit, paste special,   Add

Okay RagDyer's requires a helper column and you have several
extra steps to put things into order without the extra column.

Dave Peterson's 1st solution will work -- thought it would result in text,
but would suggest you apply the formatting first -- that way if you
had the column as Text instead of General it would still work.
His second solution is same as RagDyer's.

--
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Claire View" <froufle@DELETETHIShotmail.com> wrote in message news:%23qn3Ud4yFHA.2008@TK2MSFTNGP10.phx.gbl...
> I exported some data from an Access database.  The Social Security Numbers
> are in this format:  123-45-6789.  I would like to change them all to
> 123456789.
>
> I've tried creating a Custom Format and formatting the cells, but nothing
> changed.  Tried setting the format of a new column, then cutting and pasting
> or Paste Special into the new cells, but it keeps the old formatting.  Is
> there an answer?
>
>



0
10/9/2005 2:59:31 AM
Reply:

Similar Artilces:

How do I convert Visio to DOC or PDF format?
Company is trying to send me a Visio document but I can't open it. Is there a way to convert it to a WORD or PDF document or do I need to download VISO and if so should I get 2007 or a different version? you can download the visio viewer for free from ms. http://www.microsoft.com/downloads/details.aspx?FamilyID=d88e4542-b174-4198-ae31-6884e9edd524&DisplayLang=en al "SharenP" <SharenP@discussions.microsoft.com> wrote in message news:BE481147-A3EC-4A7B-8052-46D89214D589@microsoft.com... > Company is trying to send me a Visio document but I can't open it. I...

Formatting Axis
How do I format an axis on a chart so when choosing Auto it doesn't default to minimum 0 but actually a number closer to the minimum number in my selected range for the chart? Thanks in advance Hi, Auto means Excel decides, and for certain types of charts that will always be 0. So the answer is you can have it both ways Auto and not Auto at the same time. You can either manually enter the minimum or you can write VBA code to do it for you. If this helps, please click the Yes button Cheers, Shane Devenshire "jag" wrote: > How do I format an axis on a chart so when ch...

can a .csv file be formatted?
I have had to place all my information from a word document to a csv file. However, I notice that the formatting is not saved and the content appears in lines without any line or para breaks. The formatting is very important in the project. Each time I try to reformat, it goes back to the "non-formatted" look. Does anyone know how i can save the formatting options in a csv file? Nitha ..cvs files don't contain formatting -- Gary's Student "Nitha" wrote: > I have had to place all my information from a word document to a csv file. > However, I notice ...

Disable "remove" attachment from Notes?
By removing the "delete" privilege from the Notes entity from a security role, an User assigned with the role can be prevented from deleting a Note. But it does not prevent the User from removing the file attachment attached to the Notes. Question. Is there any workaround or intelligent customization to prevent the removing of file attachments? Hi Daniel, try limiting the NOTES - WRITE privileges to "none" Vivi - MS CRM consultant "Daniel" wrote: > By removing the "delete" privilege from the Notes entity from a security > role, an U...

Date formatting Help
Macro - Help How can you select a cell with a Sunday's Date to give you th following Sunday's Date when execute -- Db171 ----------------------------------------------------------------------- Db1712's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1591 View this thread: http://www.excelforum.com/showthread.php?threadid=27551 To get next Sunday's date, just add 7 to this Sunday. So something like: cell.value + 7 -- Regards, Fred Please reply to newsgroup, not e-mail "Db1712" <Db1712.1f88gn@excelforum-nospam.com> wrote in message ...

formatting cells to show time format
Hi I wonder if you can help me with the following. I use Office 97 Professional, and Excel 97 to do the following. I have created a spreadsheet that will be used to calculate times & results for Car Rallies. I have sorted out the formatting of the cells so they show hours, minutes, seconds, and down to thousands of a second, using this time format hh:mm:ss.000. I have done the formulas to work out the elapsed times and then total times, by taking the finish time from the start time etc, then adding elapsed times together to get total times. The thing I am trying to sort out is wh...

copy formatting?
Is there a way to copy and paste from a formatted set of cells, to an unformatted set and have the formatting copy/paste as well, so that column/row sizes adjust to be the same? Thanks Steven Shelton "Those who hammer their guns into plows will plow for those who do not!" --Thomas Jefferson Hi Steven With pastespecial you can also copy column width but not row height See the edit menu for PasteSpecial If you only want to copy a range to another place to view/print then you can try this Row and column width is working then http://www.rondebruin.nl/print.htm#n...

SSN printed with extra dashes 1 time
I have a database storing soc sec numbers and names. The soc sec numbers are entered on a form with a picture clause of 000\-00\-0000;0;_ I have a report (complicated format) that grabs information from the form and prints out the Soc Sec Number. It has a picture clause of @@@-@@-@@@@ For one client and one client only, the SSN prints out 111-2-3--5555 All others print out 111-23-5555 Does anyone know why this happens. Win XP SP2 and Access 2003 system running Thanks in advance It is very probable that the SSN that has the problem already has the dashes in it or is ot...

How to format different odd and even margins
I need to have large margins on the outer margin so different for odd and even pages? any tips? In earlier versions of Word, there was a check box in Page Setup for "Mirror margins." In recent versions, that setting is under "Multiple pages" on the Margins tab of Page Setup. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "MaryE" <MaryE@discussions.microsoft.com> wrote in message news:5875EE50-EFB9-4C55-94A3-B7AB75B7C2CE@microsoft.com... >I need to have large margins on the out...

Preserving E-mail format
I'm testing a graphic- and link-rich e-mail that I have created and want to send to my customers in Publisher. When I send the e-mail to myself and view it in Outlook, there is no problem. But when I view it through Yahoo! e-mail, the graphics are all out of place. I have tried saving the file as .html, I have tried putting everything in a table, I have tried grouping the entire page. Nothing seems to work. I want to be able to preserve the format and the links so that my customers can view the e-mail and click appropriately. Any suggestions? A great many folks will not accept...

Changing Default Chart Format
Hello all, I I'm trying to change the format a chart has by default. Basically what I'm trying to change is the grey background in the chart area, making it white by default on any new workbook I make a chart on. Can this be done? Thanks in advance, Cheers Juan Hi, Jon Peliter's article explains this. http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=299 Cheers Andy Saurjusa wrote: > Hello all, > > I I'm trying to change the format a chart has by default. Basically what I'm > trying to change is the grey background in the chart area, making ...

remove all formatting
I select a range of cells (or the entire worksheet) and from the right-click menu selecting "Format Cells" and then selecting a format category (General, Text, etc.), but the existing formatting remains intact. How to remove all formatting from a sheet ? ( Having every cell in General format is fine with me. ) Are you checking the formatting of the cells after the re-format you find that it has not changed or are you saying that, for example, a number entered as a text number and therefore right-aligned, is stil right-aligned? If the latter then re-formatting the cells wi...

OT: PE file format question
I know this is off topic but I hope someone can at least send me somewhere I can get this question answered. I am mucking with an executible file. I have change the .rsrc section and I am fixing up all the RVAs in the sections that follow and in the headers. There is still some problem however. The document that I have that talks about the PE file format says there are RVAs in the .reloc section. I don't see any. It looks like the offsets are relative the the beginning of the code. So I'm somewhat confused. I'm guessing there are still some RVAs somewhere that need to be ad...

converting works data files to excel with original formats
How can I convert data files from Works 8 to Office & Student 2007 ...

it is possible to remove the server CRM of the domain
I have problems with the CRM server and must remove it from the domain and to return it to beat, is some problem? Not quite sure what you mean. But, if you are going to actually change the domain menbership of the server, this will most likely cause problems with the config. If SQL is on a different server, then you will probably be able to get away with just re-installing CRM after adding the server back to the domain. If SQL is on the same machine, then you may be in for other troubles as SQL itself may have issues once you re-join. -- Matt Parks MVP - Microsoft CRM "Jua...

Word 2007
In Word 2003 you can define a new number format list with Number style (None). It is not possible in Word 2007 because the OK button is not activated when (None) is choosen. To create a list like this is useful if you want to repeat a text on every line in a list without a number. My work around for this time was to do a list in Word 2003 version and then copy it into Word 2007. I look forward to get an answere from someone out there. Best regards, Lisa It is true that you can't do this for simple lists in Word 2007, but you can do it for multilevel lists; see h...

Changing the format of an Excel output file made by Microsoft Access
Hello, I have a question about changing the format of a document made with OutputTo command. I have an Access database which I'm exporting some of it's data to an Excel document. Guess the Access table is named "MyAccessTable". I'm using the following command to export the data in that table to an excel worksheet and open it with Excel: DoCmd.OutputTo acOutputTable , "MyAccessTable",_ acFormatXLS,"C:\ExcelOutputFile.xls",True. What I wish to do is that after the file is opened in Excel, a procedure in Excel will run, making all the changes nee...

Conditional Formatting Question #8
I have copied data from two different databases into a workbook. I need to verify that data matches between the two sets of data and I am using conditional formatting. I have formatted a column between to sets of data to turn green if they match. My formula is =IF(a1=b1,true,false) My problem is that the formula comes up true when one column has a blank in it. No way it equals the other. I have tried different formats; number, general, text etc, with no joy. I cannot determine how the blank cell can possibly equal the other cell. The formula works for 90% of the data, it's the 10% I cannot...

How to remove an orphaned exchange 2K server from the domain?
Someone in our department made a mistake to FORMAT an Win2K DC + Exchange 2K server without cleaning it up from the domain. I am seeing its name under at least three places: - ADUC - AD Sites and Services - Exchange System Manager Q1. I believe cleaning it from the first two is easy, however I did not find an instruction about proper steps to remove a server from Exchange System Manager. Any hint will be appreciated. Q2. Except three applications that I mentioed above, is there somewhere else I should clean up as well? Thanks in advance for any help. http://support.microsoft.com/defau...

Last name True won't format
I have a spreadsheet with a person whose last name is True. This name will not format. Is there a way to make this name format like the rest of the names? Thanks Becky EXCEL 2007 Try this: 'True If my comments have helped please hit Yes. Thanks. "Becky" wrote: > I have a spreadsheet with a person whose last name is True. This name will > not format. Is there a way to make this name format like the rest of the > names? > > Thanks > Becky Thank you that worked. Becky "trip_to_tokyo" wrote: > EXCEL 2...

Format criteria for text field
I am writing an text box expression in a report as follows: =DLookUp("[BILLINGDATE]","[DATATABLE]","[InDate] =" & [Forms]![Invoices Query]![InDate]) The field INDATE is a text field. BILLINGDATE is a text field also. What delimiter do I need to add to the statement to make it work on a text field? I am getting garbage out. Must I change it to a numeric or date field to make it work? jayC, One has to ask why you are storing dates in a Text field instead of a Date/Time field. If you must keep the dates in a Text field, the format of the dates nee...

Change date format in Excel footer
I'm trying to change the format in my Excel footer to dd-mmm-yy. I have changed the short date in my regional settings from dd/MM/yy to dd-MMM-yy. In the Regional settings it shows the format I want. My Excel footer has changed from dd/MM/yy to dd-MM-yy, but still doesn't give a three-letter month. I am using Windows Vista Home Basic and MS Office 2007 -- Tibs You will have to go for a VBA solution. Set the security level to low/medium in (Tools|Macro|Security). From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search for the...

Is there a cell format for HEX numbers?
Hello. Is there a way to format cells for HEX numbers? I currently have to format them as text. I would like to be able to automatically increment in HEX, as I can with general numbers or dates. Is this possible without writing a macro? Betty wrote: >Hello. > >Is there a way to format cells for HEX numbers? I currently have to format >them as text. I would like to be able to automatically increment in HEX, as I >can with general numbers or dates. Is this possible without writing a macro? > > To display a decimal number as hex use: [ ] = DEC2HEX(A1,n) If it...

conditional format code based on lookup from another tab
Hi All, I'm wondering if it's possible to create vba code using conditional format principles with the source data on another tab. I have a separate tab for each store that lists it's personnel and their ratings. I have then summarised all of this info on another sheet using vlookup. I would like this final spreadsheet to colour code each cell according to their rating i.e. Position Store1 Store2 Store3 etc Manager Bob (red) Carol (blue) Ted (yellow) Meat Mgr Alice (blue) Bakery Mgr Can I incorpora...

conditional formatting based on the value of another cell
I want to apply a conditional format to a cell based on the value of another cell having nothing to do with the value of the cell I want the format to effect. for example - if the value in cell m5<0 i want cell a5 to be italic (regardless of the value in a5) You need to use the CF/ Formula Is ... option, rather than CF/ Cell Value Is .... then you can use Formula Is =M5<0 -- David Biddulph "bwilde" <bwilde@discussions.microsoft.com> wrote in message news:CCFB6361-8468-48B6-AB77-A628D692E5C0@microsoft.com... >I want to apply a conditional format to...