Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

excel - adding x's to social security number

Posted on 2016-09-19
15
86 Views
Last Modified: 2016-09-21
I have an excel sheet that has names and social security numbers.  I need to update all of the ss #'s to show the number like this:   xxx-98-9744.  The first 3 digits need to be x's.  Any ideas on a formula that I could copy to do this?  I'm not very proficient with excel so I will also need  instructions on how to copy this through the column so each number gets changed correctly.
0
Comment
Question by:mkramer777
  • 8
  • 5
  • 2
15 Comments
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41805114
Can you upload a small sample workbook along with the desired output mocked up manually so that we can visualize your requirement more clearly?
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41805117
A formula will put the result in a different column, assuming SS# in column B:

="xxx-"&TEXT(B2,"00-000")

Copy down the column as far as required. You can then copy all of the results and do a Paste Values back into column B if you want to overwrite the original.

Thanks
Rob
0
 

Author Comment

by:mkramer777
ID: 41805133
Can you show me a screenshot of an excel file that would demonstrate this for me?  Very novice with these things.  Right now I have a column of 50 social security numbers in column B.  Where do I add the formula and how do I copy it to the whole column?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 41805193
Copy the formula I gave into cell C2,  assuming it is blank. Hover the mouse over the bottom right corner of cell C2 until the cursors turns to a + symbol; double click and the formula will be copied down as required.

Now select cell C2 as normal with the mouse. On the keyboard press the Shift key and the down arrow key until all the cells with the formula are selected. Now press Ctrl + C to copy these cells. Select B2 and right click. There will be an option to paste values, looks like a square with 123 in it; click that and the values will be pasted in column B. The formulas in column C can now be deleted.
0
 

Author Comment

by:mkramer777
ID: 41806988
I must be doing this all wrong.  I tried to go by the instructions but the screenshot shows what I received.
Screen-Shot-2016-09-20-at-11.05.31-A.png
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41807019
Can you upload the file so that we can take a look.

From the screen shot, it looks like the value of 502-98-8984 is in cell B1. For my formula I had assumed that row 1 would be headers so suggested you refer to row 2 in the formula for the first value.

Also it looks like you have the value with 9 digits spaced with a couple of dashes, that is not the impression I got from the question.

So, I assume you want the first three characters converted to xxx and the remainder added to the end. Is that correct? Are the dashes as a result of special formatting or actually contained in the cell; I know there is a Built In format for SS#s.

When cell B2 is selected, what is in the formula bar, is it '502-98-8984 or just 502988984?

Thanks
Rob H
0
 

Author Comment

by:mkramer777
ID: 41807040
It is just one column of social security numbers.  Need the formula to format the first 3 numbers of the ss # into x's so the number will look like this:   xxx-111-1112

Here is a screenshot.  Probably explained it wrong.
Screen-Shot-2016-09-20-at-11.18.17-A.png
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41807043
That still doesn't answer the question of what is in the cell.

If your values are just numbers, in C1 use:

="xxx-"&TEXT(MID(B1,4,6),"00-0000")

If your values already have the dashes, in C1 use:

="xxx-"&MID(B1,5,7)

Then copy down column C and copy and paste values into column B as mentioned before.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41807053
Your latest screenshot (not useful, files are better) shows the values in column A rather than column B as per first screenshot.

Change reference to B1 in formulas above to A1.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41807067
Slight amendment, I had the format of the SS# wrong, I was missing a digit.

If just numbers in the cell use:  
  ="xxx-"&TEXT(MID(B1,4,7),"000-0000")

If already spaced with dashes use:
   ="xxx-"&MID(B1,5,8)
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41807120
Another way....
As per your screenshot, If your number string is in A1, try this in B1

In B1
=SUBSTITUTE(A1,LEFT(A1,3),"xxx")

Open in new window

0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41807138
Assuming SS# is already formatted with dashes and not  a real number with Custom formatting. Still waiting for an answer on that question.
0
 

Author Closing Comment

by:mkramer777
ID: 41807305
Got it.  Thanks!
0
 

Author Comment

by:mkramer777
ID: 41807325
Sorry Rob.  CLicked on the wrong area to contact you.  I need the formual to format the ss # so it displays like this:

xxx-xx-8785
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41807530
="xxx-xx-"&RIGHT(B1,4)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

789 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