Solved

Excel 2007 Formatting Column of Numbers to Social Security Format

Posted on 2014-02-10
2
308 Views
Last Modified: 2014-02-11
Hello,

I have a column of numbers (column A) that are social security numbers and need to be in SSN format.  For some reason the custom SSN format is not working on these numbers.  They appear in SSN format in the column but on the formula/number bar they are missing the dashes.

Can someone provide a formula that will look at 9 digits of numbers and add the necessary dashes so its in SSN format?  Im guessing a left mid right string?

Thanks!
0
Comment
Question by:Escanaba
2 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
Comment Utility
If you format the numbers as SSN you will see the number without dashes in the formula bar.

You will need to add a formula in another column if you want the actual cell to contain the dashes...

e.g.

=TEXT(A1,"000-000-000")

You can then copy this column and paste special >> values over the original, and delete the formula column.
0
 
LVL 23

Expert Comment

by:DanCh99
Comment Utility
You can also select the cells as they are, and go to Format Cells.. Number.. Custom and set the custom format as:
000-000-000
This should insert the dashes for you, but leaves the underlying numbers exactly as they were.  On the sheet itself they will display correctly, but the formula bar will just show the "bare" number.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
clear from current cell down not up 2 10
second highest value difference 11 24
Add macros on Open 9 22
Macro to delete column 3 0
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now