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
1298 Views

Similar Articles

[PageSpeed] 44

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:

formating text cells
I need to format a text cell using bullets similiar to Word's bullets and numbering? Any idea on how to do that? Can Excell automatically generate the bullet? Excel doesn't support this the way Word does. I'd use Word, if at all possible. One alternative in Excel: If you put the text of the bullet point in B1, put this in A1 to generate a "bullet": =IF(B1<>"",CHAR(149),"") Copy this down as far as needed. You will likely want to use Align Right on Column A as well. Not a bad idea to adjust the width of Column A, either. tj "Cathyin...

Remove Public Folder email address's
Hi After a pulic folder is created in error as email enabled is it possible to remove this function, so that it is not email enabled. Cheers Paul "Paul" <Paul@discussions.microsoft.com> wrote in message news:222C07F9-C163-4A4C-9E3F-AD536D258350@microsoft.com... > Hi > After a pulic folder is created in error as email enabled is it possible to > remove this function, so that it is not email enabled. > > Cheers > > Paul Right-click the folder in ESM. You should see All Tasks->Mail Disable. Lee. -- _______________________________________ Outlook ...

Custom Date Format
I want to format a series of cells with a custom date format that is similar to mm/dd/yy but I want the year to always be 2003. I tried creating a custom format mm/dd/03 but Excel didn't accept that. What's the best way to do this? Peter -- **Remove Xs to reply by e-mail** One way: Format/Cells/Number/Custom mm/dd/"2003" In article <sdhsg0l53vgbjgvt9sqfiln6jb164qb1im@4ax.com>, Peter <XXpmpmpmXX@mindspring.com> wrote: > I want to format a series of cells with a custom date format that is > similar to mm/dd/yy but I want the year to always ...

Removing macros from an Excel spreadsheet
I have an Excel spreadsheet which contains a macro and am trying to create a simplified version of it without the macro. I have tried to remove the macro and have resaved the file under a different filename. My problem is that whenever I try to open the new file (which no longer has the macro) I still get the "macro warning" box appearing each time. I have tried removing the macro in several ways: i) Using Tools, Macro, Macros, Delete and ii) also using Alt-F11 to open the VB Editor and then selecting the Module in the "projects" browser, right clicking and choosing...

how to enable a GP user to add/remove/change other users??
We have a GP 2010 user who wants to add, remove, modify users, including which companies they can see. For now I'm forced to give her sa access, which is not good... How can I modify this user account so she can add/remove/modify users and configure which companies they can work on?? She already has PowerUser privileges but this is apparently not sufficient. Then I can change the sa password and keep that account safe... Thank you, Tom Follow these steps to enable POWERUSER to add/modify users: http://dynamicsgpblogster.blogspot.com/2009/04/microsoft-dynamics-gp-10-pow= eruser-role.html ...

Removing Quotes, Opportunities, Invoices Tab
Is there a way to remove the quotes, opportunities and invoices tab from the 'Sales' section of the 'Account' record ? Our client does not use any of the opportunities, quotes and invoices records and those tabs are very disturbing. If not, is there a way to change the tab order ? Any help will be appreciated. Thanks Nicos Papaioannou Simply remove all security privileges for all user roles on those objects. The tabs will vanish "Nicos Papaioannou" <papaioannoun@dotcy.com.cy> wrote in message news:d72839e3.0409100831.20bc94c@posting.google.com... > Is ...

CELL/NUMBER FORMAT
EXCEL. When entering a whole number into a cell EXCEl is dividing that number by 10 immediatly after entry. If I place a decimal point after the number this does not happen. If I enter a number less than 1 the number is entered normally. It appears that EXCEL needs a decimal point to enter the number correctly. I have used EXCEL for many years (current version from 2004) and this has never hapenned. Any Ideas out there guys? ...

Condtional Formatting
My conditional formatting applies to cells (see row numbers): =$B$41 =$B$42 When I insert 10 rows between row 41 and 42, the condition now applies to cells as follows: =$B$41:$B$51 1. Is there a way that the condtions will not be copied to rows 42 to 51? Or, the condition will apply only to each cell, i.e." =$B$41 =$B$42 =$B$43 etc. to =$B$51 Thank you. Use the absolute column reference of $B and the relative row reference of 41 to get the seconf optiont. =$B41 To not have the CF copied to the inserted rows, turn off the option to "exte...

Formatting cell questions
1) I copy/paste numbers from Access table into a w/sheet. I try to calculte the sum and I cannot. I format the cells to numbers and still cannot get a total. Why? 2) After copy/paste a green triangle appears on the upper- left corner of the cells. I can't find anything about it. Thx!! Hi Philippe Copy a empty cell Select the cells with text numbers Right click on the selection Choose Paste Special Check Add Ok http://office.microsoft.com/assistance/offhelp/offxp/excel/xlmain10/html/TrianglesInCells.htm Background error checking in Excel 2002 is controlled by Tools, Options, Error C...

Currency format in sort
I am working in Excel 2003. I have numeric data in column A that gets sorted. I would always like currency format in the first cell (A2) under the heading in A1. If I apply the format to A2, but then sort, the number with the currency format from A2 moves around and the new number in A2 no longer has currency formatting. Is there a way to lock in the format for cell A2 and still be able to sort? I don't want to have a separate column for the dollar sign. Thanks, Ricki I couldn't do it. But if you do this a lot, maybe you could record a macro when you: select your data sort b...

Conditional Formatting #12
I have a spredsheet set up with conditional formatting as it relates to calendar dates. My question to the world is can I add something to the formula that will "add some text" when the condition is reached. Right now the cells just change color. V/R Walt ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Add a separate IF statement for those cells where a condition is to be evaluated. >-----Original Message----- >I have a spredsheet set up with con...

Data validation on a custom format
Hi again everyone.... I am currently using the following custom format: [h]:mm How can I use data validation to prevent invalid data from being entered into a cell of that format?? For my purposes, I only want data which consists of 1 or more digits in the "hours" field, folowed by one colon, followed by 1 to 2 digits in the minutes field. Do I need to enter some sort of formula into the data validation field?? thank you! There's an option in the Data|Validation|Settings tab that you can set for Time. It sounds like you ...

Format Lines
Would anyone be able to tell me why when using the drawing toolbar in excel I can not use things like 3d settings and Dash style. They are greyed out. Thanks Steve Never mind....I guess you have to put a normal line in first. Thank you. "Steve H." <steveh@mrinstitute.org> wrote in message news:emVotnOzEHA.3028@TK2MSFTNGP10.phx.gbl... > Would anyone be able to tell me why when using the drawing toolbar in excel > I can not use things like 3d settings and Dash style. They are greyed out. > > Thanks > > Steve > > ...

Conditional Formatting Based on Date
Allow me to establish the context of the situation. In column A I have a date that a form is received. In column B I want to condition the cells to display a color contingent on the amount of days that have transpired since the form was received. Column B actually documents whether a form has been approved or not. If it has not, then the appropriate color is displayed. If the form is approved, then the date of approval is put in the cell with no need for any color. The color is simply to act as an indicator of the amount of days that have passed without approval. For example, let's say ...

Date Formatting #6
Please can somebody help me. I need to disable date formatting in Excel 2003 & 2007. Excel's "helpfullness" is being VERY unhepfull at the moment. It is changing my style codes to dates, and if you format the cell to text then it applies some useless number that means nothing to me instead of what is supposed to be in that cell. I need to open already created csv files - so formatting the cells beforehand isnt an option. Please can somebody tell me how to disable date formatting entirely in Excel. The application dumps data to csv files. Users manipulate this data and wo...

Protection from format changes allowing form to be filled in
I have a questionnaire matrix to be filled in by others. I would like to protect the form from changes in format. However, others should be able to fill in the form by choosing from drop down list and/or writing in cells. The way I have been trying is: tools, protection, protect sheet, insert password, ok. Tried variations with contents, objects, scenarios boxes all ticked/partically ticked/blank. However, in all cases, the sheet becomes read only and does not allow to be filled in. Thank you for your help. Unprotect the worksheet (if you have it protected). Select the cells that the users ...

Formatting problem #3
Hi, In thspreadsheet, I'm able to format only one cell but not other cell besides this cell. Could anyone tell me how I could fix this problem. Thanks. Rajan -- Message posted from http://www.ExcelForum.com Hi Rajani More imformation required. Can you select other cells?, the worksheet may be protected. Bob C. >-----Original Message----- >Hi, >In thspreadsheet, I'm able to format only one cell but not other cells >besides this cell. Could anyone tell me how I could fix this problem. >Thanks. >Rajani > > >--- >Message posted from http://www.ExcelFor...

An odd question about formatting
Ok, here's a toughie. I have a list of numbers in column A and I would like in column B a smiliar number of asterisks like this: 4 **** 1 * 9 ********* Any ideas? Amy Give the REPT Repeat function a try, I think it will fulfill your needs nicely. "cvgairport" wrote: > Ok, here's a toughie. I have a list of numbers in column A and I would like > in column B a smiliar number of asterisks like this: > > 4 **** > 1 * > 9 ********* > > Any ideas? > > Amy =REPT("*",A1) -- David Biddulph "cvgairp...

Excel 2000: File format of Unicode text files
In Excel it is possible to save a worksheet as 'Unicode text'. Is there a description of the file format of these unicode text files? The problem is that there are some quotes (") added, if the cells content e.g. a line feed or quotes. So there may be some additional cases, where unexpected content is added by Excel and this would be very helpful to know. Thanks -- Rolf Keller I don't think MS has documented their file format publicly. Your choices may be to do the experimenting yourself to deduce their format, or to use a macro to create the Unicode text file. Here...

Format Cell Dialogue Box
Hi there, Why don't you make the Format Cells dialogue box modeless. So that you do not have to keep opening and closing it. Jimbola There are no "you"'s here, if your post is referring to Microsoft developers. This is a peer to peer News Group manned(personned?) by volunteers and not by Microsoft employees. Your "modeless" idea is a good one, however................ There are many shortcut keys for formatting cells without opening the dialog box. Perhaps one or more of them would suffice. See answer wizard on "shortcut keys" Gord Dibben Excel ...

Format Function
Below is a snipplet of code that gets a recordset. I'm trying to output to a text file and keep my columns of data straight by using tabs. Because of the length of records in the field objRS!sShift, the vbTab code wasn't lining my column correctly, so I used the Format() function with the "@@@@@@" parameter and my data from the objRS!sShift now right-aligns. My new problem is I have a new field called "percentData" that is a percent that I need to display as 54.62% for example. Without the Format() function, the raw output of this field looks like 0.5462625 f...

Format custom number: C-0000-L
Hi, I need to format a few cells in Excel to look like C-0000-L where "0000" represents four numbers and "L" represents a letter. I tried to do it in format - custom with C-0000-@ but it gives me an error message. I also tried to do it using the concatenate function but it doesn`t work. Can anyone please help me? Thanks! -- Sonia Custom format "C-"0000"-L" -- David Biddulph "Sonia" <Sonia@discussions.microsoft.com> wrote in message news:8EAB02FD-1AA5-4E80-880A-7C9A33A8FE01@microsoft.com... > Hi, I need to format...

Conditional Formatting and selection
I have one column that has both name/address(in one row) and telephone numbers(in another row). I'd like to move the name/address info into it's own column. I've been able to do a conditional format to change the text info to a new color but I can't seem to mass select that info so I can move it all at once. Any ideas would be appreciated. if either the names or the telepphe numbers are all in even rows insert a helper column and enter =mod(row(),2) and copy down to the end of the data use autofilter on this column and select 0 select all and paste somewhere change the aut...

Formatting changed when received by version 97
Hi, I have a problem with my Outlook XP. I am using Outlook XP with SP2 installed and using Word as my email editor. My problem is that when my emails are received by a 2000 or XP version of Outlook, all text is displayed correctly, but when a version 97 receives my mail then the font is changing mid sentence and extra blank lines are being added. The whole email look a complete mess. I know 97 only uses text and not HTML so something must be wrong with the way 97 removes the HTML tags. Has anyone had this problem or know of a fix? TIA Mark It is not so much that Outlook 97 does not ...

Combining data (numeric format) in multiple cells into one cell (t
If you have data in multiple cells that is numeric format, can the data be combined into one cell as text? Example. Cell A1 is number of correct answers (9), Cell B1 is number of questions (10). In cell C1, would like to display data as (9-10)? Can a formula be created that will automatically do this so as to eliminate having to type the information? one way: =A1 & "-" & B1 In article <018B8827-4277-4C02-81E1-3BEDB61422A0@microsoft.com>, "GNAC SID" <GNAC SID@discussions.microsoft.com> wrote: > If you have data in multiple cells that is num...