Jump to content


Photo

remove dashes from ssn, keep zeros

leading zero

  • Please log in to reply
2 replies to this topic

#1 Jenny M

Jenny M

    Advanced Member

  • Members
  • PipPipPip
  • 32 posts

Posted 11 July 2016 - 01:15 PM

I have a list of social security numbers, formatted xxx-xx-xxxx.  I need it without the dashes.  The column is formatted as text to keep the initial zeroes.  I tried find and replace, replacing "-" with nothing.  The initial zeroes disappeared.  I tried text to columns (formatted as text); the initial zeroes disappeared.

 

This is probably an easy fix, but I'm stumped.  I'll be manually removing the dashes until someone can set me straight!

 

Thank you!



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 886 posts

Posted 11 July 2016 - 01:54 PM

Hi Jenny,

 

If all you need is to remove the dashes, you might find that the SUBSTITUTE function is what you need. SUBSTITUTE always returns values as text, so you shouldn't have any problems with leading zeroes.

 

Here's the SUBSTITUTE formula to remove all dashes from the value in cell E2:

=SUBSTITUTE(E2,"-","")

 

I have attached an example workbook showing this in action:

 

Attached File  SubstituteSSN.xlsx   10.01KB   310 downloads

SubstituteSSN.png

 

You could also do this using the Flash Fill, Find & Replace or Text to Columns features, as you mentioned. The problem of Excel removing the leading zeroes can be solved by using a custom format to tell Excel to display the correct number of leading zeroes. For example, to always display 9 digits you could use the custom format: 000000000

 

You can see more about how to use custom formats in the video lesson: Essential Skills Lesson 4-4 Create custom number formats.

 

You can also see how to use the other features in the following lessons:

For how to use Flash Fill, see the video tutorial: Essential Skills Lesson 2-18 Use automatic Flash Fill to split delimited text.

For instructions on how to use Find & Replace in Excel, see the video lesson: Essential Skills Lesson 6-8 Use find and replace.

For a walkthrough of splitting cells with Text to Columns, see: Expert Skills Lesson 2-2 Split delimited data using Text to Columns.

 

I hope you're now able to get the results you need, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Jenny M

Jenny M

    Advanced Member

  • Members
  • PipPipPip
  • 32 posts

Posted 11 July 2016 - 02:09 PM

THANK YOU!  Substitute worked perfectly.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users