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

Similar Articles

[PageSpeed] 20

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 (12004)
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:

Change Format #2
How can I change a cell with general formatting data to a date format? As an example, I have in cell A1: 20040205 and I would like for it to read: 2004/02/05 (i.e., Feb 2, 2004) Hi Steve changing the format to DATE just goto 'Format - Cells' and choose your date format. Though this will not change your entry '20040205' to a valid date. You have to enter the dates with a delimiter or use a macro whcih will do the conversion for you (see http://www.cpearson.com/excel/DateTimeEntry.htm) Frank Steve wrote: > How can I change a cell with general formatting data to a > d...

Remove Excel Icon ??
Hi, I would like to remove the possibility to export data in Excel. I think I already read it's impossible but I just want to confirm. Thanks, Sylvie Your correct, there is no supported way to do this in the current release of Microsoft CRM 1.2 -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "sylvie" <sylvie.mondoux@proximi-t.com> wrote in message news:09dd01c47b03$d3627560$a301280a@phx.gbl... > Hi, > > I would like to remove the possibility to export data in > Excel. I think I already rea...

Removal
How do I completely remove all traces of Outlook 2002 for a complete fresh reinstall. I can't find anything for '02. HELP!!!!!!!!!!!!!!!!! ...

named ranges in conditional formating inside a pivottable
Hi (excel 2007) In my pivottable column k show revenue Column l show me the sales responsible. If the revenue is less than e.g. 1,000 I want the name in col I to be red. Using conditional formating I refer to the cell k13 (the upper cell i that column). I would prefer to name that cell "revenue", but I can not refer to "revenue" in the conditional formating. What do I do wrong ? Regards Peter I just did this in an ordinary sheet but it should work in a PT Select the Names to format and choose, Conditional Formatting, Choose Formula Is, (In 2...

Conditional Formatting Text!!
Hi I want to conditionally format some text in a spreadsheet using formula: I have column A1 with: 4a Be 3a To 4c To 4b Be 4c Be 5c Be 5c To 6a Be etc... What I want to do is for all cells which contain "5a Be" "5b Be" 5c Be" "4a Be" "4b be" and "4c Be" to be highlighted in Red - is there a formula to put all of them into one formula - If("5a Be" or "5b Be) etc.. Thanks Kiran "Kiran" <kiran.vithal@gmail.com> wrote in message news:1143053337.964222.63000@t31g2000cwb.googlegroups.com... > Hi > ...

how do I remove spaces from cells that were pasted
I am pasting numbers into a spreadsheet, however its treating them as text because there is a space before the number. How do I remove the space so it treats it as a number ? I have used the =trim() function and its not working. Thanks for any help ! Hi! It sounds like you're copy/pasting from a website. Try this macro from David McRitchie. Look for TRIMALL, it's about half way down the page: http://www.mvps.org/dmcritchie/excel/join.htm Biff >-----Original Message----- >I am pasting numbers into a spreadsheet, however its treating them as text >because there is a...

Remove lines with +++
Is there anyway to find any line that has a + in it and delete that whole line?! I'd also like to do the same for *. I have a list of about 1300-1500 names and addresses. Some of them have a few +++ next to the name and some of them have a few *** next to the name. These were put there by the company to designate things. They need to be removed from the list, but it's a lost of work doing it one by one. You could apply an autofilter to the column with these characters in and from the pull-down select Custom - in the panel choose "Contains" (scroll down for this) the...

Lookup
I currently have a lookup field in my spreadsheet that looks up the price of a product by product number. Although the lookup usually works there are occasions when it returnes the N/A even tho the product number is in the lookup table. If I copy the product reference from the lookup table and paste it into the first spreadsheet, the lookup then gives the correct price. I've tried formatting the product reference field in the spreadsheet and in the lookup table to try to get them to match, but i still get these errors. Can anyone shed any light? thanks Gill --- Message posted fr...

Outlook 2003 Task View formatting
Does anyone have a way to format a Task item when in Outlook Task view, such as, highlighting a particular Task row? yes. Explain what you want to do. Or just go and look at the Automatic Formatting function. Regards Judy Gleeson MVP Outlook www.judygleeson.com www.deskdoctors.com Are you sick of bad email practice? Get a copy of my paper "Implementing Email Policy" from the Desk Doctors website. "brightenmyoutlook" <rjbeaupre@gmail.com> wrote in message news:278e5a3c-63c3-479a-bc15-391e492495b3@v36g2000yqv.googlegroups.com... > Does anyone have a way to f...

How to Remove SRS
How to I remove or disable Site Replication Service (SRS) from my Exchange 2003? Thank You, Jason HI Remove the SRS by expanding the Tools node in ESM, right-clicking Site Replication Service, and clicking Delete "jpollock" wrote: > How to I remove or disable Site Replication Service (SRS) from my Exchange > 2003? > > Thank You, > > Jason > > And make sure you are doing it while logged in to the console of the Exchange server on which the SRS is running. -- Jim McBee Web: http://www.somorita.com Blog: http://mostlyexchange.blogspot.com &...

allmost done with removing our 1st exchange 03 server
We are in the process of removing the 1st exchange server in our domain. Everything has been replicated over to a new server and all mailboxes have been moved. When we shut down the original server down, email still works great for 90% of the team. For the rest of us, when type the name of person to receive an email, outlook it still tries to resolve the name on the original server. I checked the profile on the users mailbox and it shows them pointing to the new server. Any suggestions would be appreciated. Try creating a new Outlook profile. Also, when you shut down you're...

remove Fax from address book?
Is there a way to stop showing Fax in the address book? I have a lot of fax numbers in my Contacts. I do not want to loose them - just not show them in the Address Book. thx Outlook considers fax numbers to be valid electronic addresses, since there are many client- and server-based components that can use such addresses. One method to hide fax numbers from the address book is to prefix the fax number with one or more letters (maybe B for business fax, H for home, O for other). If the fax number begins with a letter, Outlook won't show it in the address book. There are a couple of t...

Removing an item from menu
How can I remove an item from the menu and add some thing else. For instance, how can I remove inactive from the menu in contact page and put some other item with my own code behind it? You can hide the public views by simply creating a team (non used views or whatever you want to name it) and share the view to that team(done in the customize entity fields in the forms and views), this will make it a private view that only members of that team can see. You can also create new public or private views in the same place. "Amin" wrote: > How can I remove an item from the m...

Cannot Remove a program from Add Remove via remove.
I downloaded a program Fast At Last and it did not download properly and I could not remove it. Even System Restore did not remove it. The program is listed as 924PL32. Kept getting messages The feature you are trying to use is on a network resource that is unavailable. C:\dell\GC605. Seems Dell is the publisher but they would not give me free support on this issue. Any suggestions as to how I can remove the program? I believe it may be associated with spyware. When I ran spysweeper it removed some Rouge Security products but I still cannot remove this program. Cor...

Excel TEXT formatting
Hello, I want to use TEXT function in excel to format a number like -99.9854 to xxxxxx-99.9854 where 6 x's are 6 trailing white spaces. The number is in the range -99999.9999 to -0.9999. Any body can help please? KIS On Tue, 15 Mar 2011 16:15:50 -0700 (PDT), kis <kisitanggang@gmail.com> wrote: >Hello, > >I want to use TEXT function in excel to format a number like -99.9854 >to xxxxxx-99.9854 where 6 x's are 6 trailing white spaces. The number >is in the range -99999.9999 to -0.9999. Any body can help please? > >KIS > > > > Maybe it&#...

Need to remove selected characters
I have 25000 cells with map coordinates in it in the following format: N34* 51.87' I need the data in the following format: N55 13.26 So basically I need to strip out the * and ' from each cell. Any simple way to do this? I sure would appreciate any assistance. Thank you -- Dale Hi! Select the range of cells in question. Goto Edit>Rplace Find what: ~* Replace with: nothing, leave this blank Replace All Repeat the process for the ' It's VERY important that you select the specific range before you attempt this!!!!!!! Biff "Natedanger" <Natedanger@d...

Remove deduction code from payroll stub
Is it possible to remove just a single payroll deduction code from a payroll stub using 'Employee Checks Stub on Top-D'? For example, an employee has three deductions: Insurance, 401k, Medical. Would it be possible then to show insurance and 401k but leave medical off the check stub for all employees who have medical? Thanks in advance for your help! I don't believe so because the deduction field is an array so you'd have to know which array value. Even if you did, I don't think you can use arrays in report restrictions. -- Charles Allen, MVP "drose03...

RW and conditional formatting
Ive created a bought inventory label - works a treat - id like to add a circle to highlight some of the data on the label - but i only want the circle the information when it appears - only for about 10% of items.. is it possible to put a conditional on a circle?? i think not but im asking!! Thanks Theo :) Theo You cannot place a conditional value on the circle, but one possible way around this is to create to bands (two headers for instance) which prints exactly the same data. Then place a condition on the band to only print one or the other, based on your criteria. "Theo&q...

How to Remove Yahoo toolbar
I have three toolbars installed on IE8-Yahoo (to the right of the address box), and Google and AVG below the address box. I want to uninstall the Yahoo toolbar but how? It is not listed under 'Add/Remove' programs and there is a magnifying glass and arrow to the right of this toolbar that says 'Manage search providers' so I removed Yahoo toolbar from there but it is still showing. I uninstalled Yahoo messenger (since the toolbar was included during the Messenger install so it would be removed by deleting Messenger) but it is still there. I know that for AV...

Removing password protection on a sheet
A co-worker of mine protected a sheet in her workbook but lost th password. I help her with formula/script issues and I thought I coul simply copy the data on this sheet and paste it to another sheet to ge around it, but it won't let me select anything other than a few cell (based on the cells settings I'm sure). I know that Microsoft doesn' support removal of passwords when you don't know the password (make sense from a secuity standpoint) but was hoping someone could help m out here. She can remake it if need be but thought I'd ask ;) Thanks -- Otto.ts ------------...

Dropdown Formatting
What does control the format of the dropdown options in data validation. Especally if the dropdown is an indirect to another workbook? We are getting inconsistant results. Debra Dalgleish has some instructions at: http://www.contextures.com/xlDataVal05.html#Dynamic (the other workbook is still open, right???) DeltaCharlie wrote: > > What does control the format of the dropdown options in data validation. Especally if the dropdown is an indirect to another workbook? We are getting inconsistant results. -- Dave Peterson ec35720@msn.com ...

conditional formating dates, please help me
Sorry but this is a multiple part question. The spreadsheet in question is a tracker for incoming tasks. column D is the received date(dd-mmm-yy). I want to highlight the new tasks received with in the last 72hrs. so basically equal to or 72 hrs less than todays date. Column E list the due date(dd-mmm-yy). I would like to highlight the cell that are due in the next 7 days red and 8-30 days amber. Column K shows the status as "complete" or "in progress". it is already conditionally formated to highlight green when "complete" is entered and ye...

Removing old computers connector exchange 2003
Hi, I have a new exc 2003 server that is working fine. i keep getting errors in the event log that it cannot connect to an old exchange server that was removed. for some reason, in the connectors, i can see two connectors that point for the old server. connector for ms scheduleplus(oldservername) connector for ms mail (oldservername) i cannot delete them from there. what can i do? Omer On 4 Oct 2006 23:43:14 -0700, "etgarnet" <etgarnet@zahav.net.il> wrote: >Hi, > >I have a new exc 2003 server that is working fine. >i keep getting errors in the event log that ...

Custom Number Format problem
I need a special number format, but I can't seem to get what I want: IF a number is an integer, then there should be NO decimal point displayed. IF a number has a decimal part, then if should display only one digit for it. For instance: 3 -> 3 4.34 -> 4.3 2.88 -> 2.9 34 -> 34 5.01 -> 5.0 Is it possible to do this with a custom number format ? THANKS ! R P.S. I would prefer NOT to use any VBA to do this! Maybe. But easier would be: =IF(MOD(A1,1)=0,INT(A1),ROUND(A1,1)) and format as General Of course you can replace A1 with your original formula, but then it is cal...

Adding and removing removable drives
Hello NG, I wrote an FolderView ActiveX control. How can I receive a notification for adding or removing drives for example usb sticks or other media? And how can I receive a notification to refresh network shares? Speial thanks for help. Thomas Bendrich Is this what you are looking for DBT_DEVNODES_CHANGED. AliR. "Thomas Bendrich" <admin@thb-net.com> wrote in message news:u7Gb2wjuFHA.2312@TK2MSFTNGP14.phx.gbl... > Hello NG, > > I wrote an FolderView ActiveX control. How can I receive a notification for > adding or removing drives for example usb sticks or ...