formula to strip numbers 2

[TaiChi56]

I have a program that I use to keep track of students. I copy and paste this program into an excel sheet. I want to be able to take the first five (5) numbers of a social security number and strip it from the sheet, thus leaving the student's last four digits. I need to be able to do this as a batch file. Sometimes I have up to several hundred students I need to do this with. Right now we do it manually. Any help would be appreciated. Thank you.

The secret in education lies in respecting the student. {Ralph Waldo Emerson}.

 

[bkpchs237]

TaiChi56,

Place all your SSNs in one column, for example from cells A1 thru A200. Then in cell B1 put the formula =right(a1,4)
and copy that down the column. Now highlight all the cells in column B and choose, Edit, Copy, Edit, Paste Special, Values, OK. Now you'll have your list and can delete column A or leave it as you see fit.

This in effect would perform the task within one file. A few more details on what you mean by "...I need to be able to do this as a batch file. Sometimes I have up to several hundred students I need to do this with..." would be helpful.

Hope this helps.

 

[TaiChi56]

Thank you for the help. What I mean about several hundred students, is this. I have an excel sheet: 1st column has the social security number of the student, 2nd is their last and first name, third is selected information. Unfortunatley the program that was bought to track information does not let me do several things. So I copy that information into my Excel workbook.

By law we cannot give the complete social security number to anyone so we cannot post it. Our population we have many kids with the same names. So I need at least the last four digits to show. I do not want to highlight each number one row at a time and delete the first 5 digits. That is why I was saying that a formula to strip the first 5 digits in each students social security number would be a great help.

The secret in education lies in respecting the student. {Ralph Waldo Emerson}.

 

[g3a3n3n3]

TaiChi56,

Assuming your Students SSNs sit from A1 to A200 then in an empty cell say B1 you enter

=MID(A1,6,4)

This Mid formula should work for you... the syntax has three main parts to it.

= MID(1,2,3)
where
1. Cell number of the number/text you want to crop (cell A1 in our example),
2. The starting point within the number (as in keep numbers/text from the 6th digit onward in our example) and
3. The number of digits/letters you want to keep (4 in our case as we need the last 4 numbers)


Hope this helps

Gillian

 

[Bowers74]

Gillian,

In some countries, the social security numbers are split up with characters (i.e. USA 123-45-6789), this means that your MID() formula will not work in such cases.

TaiChi56,

bkpchs237 actually has the best solution for you. You will have to manually create a new column and use his RIGHT() formula. Copy and Paste Values, and then delete the column containing the full SSN.

But now a suggestion from me:

If the SSN#'s are separated by a specific character ("-" in my exapamle), then you can use the Text to Columns feature in Excel.

Select the column that contains the SSN#'s and then go to Data->Text to columns...:

1. Select Delimited and click Next
2. Uncheck all of the checkboxes in the Delimiters frame (except Other
3. Enter a hyphen "-" into the Delimiters frame in the Other field.
4. Click Next
5. For the columns that you don't want to have shown: Click the column and then select Do not import column (skip)
6. Press Finish

If you've done this correctly, you should only have the last four numbers of the SSN#'s.

I hope this helps!!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[KenWright]

I think Mike is on the right track, but you could simply use Data / Text To Columns / Fixed Width, regardless of whether there are any delimiters or not.

As long as every number is the same length, then do Data / Text To Columns / Fixed Width / Insert a break where you want to split the number and hit OK till done. This will do all of your data at the same time.

Make sure you have a blank column or two to the right of your data (ie the column you are splitting), else it will get overwritten. You can simplyt choose not to import the part of the column that you are chopping off whilst in the wizard.

Regards
Ken...............

 

[TaiChi56]

Excellent you guys are great.

The secret in education lies in respecting the student. {Ralph Waldo Emerson}.

 

[g3a3n3n3]

I guess I should have mentioned I'm from Australia and I'm not very farmilar with social security numbers!!!!!

- Gillian

 

[Bowers74]

Understandable Gillian, but in any case, if you want to use a formula to return the LAST 4 (or however many) characters from a cell's value you should always use the RIGHT() function.

=RIGHT(A1,4)

No Harm, No Foul! ;-)



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[g3a3n3n3]

yup I know the RIGHT() function exists and dont ask me why but I just love the MID function !!!