Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Social Security numbers
Sometime ago I asked about having a column a numbers (soc sec numbers) and
one more numbers in them, making a total of 10 numbers. I wanted to strip the last digit. I was helped by using the following function: =left(a1,9), then I was instructed to copy the whole column where the 9 digits were and paste them specialvalue to another column. That way I have the value and not the formula. However, when I want to format the resulting value column to represent a social security number, it does not accomplish it. I have two problems: When stripping the last digit the function LEFT does not take in account a left zero (leading zero) and, How can I format the resulting number to represent a social security number? TIA Hernan |
#2
|
|||
|
|||
If it is because your leading zeros are dropping off and leaving you with a shorter string try =LEFT(B4,LEN(B4)-1) What format are your social security numbers in? R -- Ruthki ------------------------------------------------------------------------ Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503 View this thread: http://www.excelforum.com/showthread...hreadid=392746 |
#3
|
|||
|
|||
Hi Ruthki,
Ok, I'll try that. And the format the social security number is 000-00-0000 Thanks. Hernan "Ruthki" wrote: If it is because your leading zeros are dropping off and leaving you with a shorter string try =LEFT(B4,LEN(B4)-1) What format are your social security numbers in? R -- Ruthki ------------------------------------------------------------------------ Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503 View this thread: http://www.excelforum.com/showthread...hreadid=392746 |
#4
|
|||
|
|||
If all values are numbers I would use a custom format to format the number into what I wanted. But first I think you need to change the text string to a value by surrounding it with the Value formula so you end up with =VALUE(LEFT(B9,LEN(B9)-1)) Then with a custom format - select Format, Cells then Custom at the bottom of the available list. Type in 000-000-0000 in the box below the word Type on the right hand side - this should then format your numbers appropriately. Remember to format all the cells which will contain your social security numbers. Alternatively you can use a mix of Left() mid() and Right() functions combined together with &"-"& R -- Ruthki ------------------------------------------------------------------------ Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503 View this thread: http://www.excelforum.com/showthread...hreadid=392746 |
#5
|
|||
|
|||
Hi Ruthki,
Yes, I try to format the column under custom, that's where the soc sec format is along with zip code etc... This hint of using =VALUE(LEFT(B9,LEN(B9)-1)), I think will do the trick. :-) Thanks and I'll let you know. "Ruthki" wrote: If all values are numbers I would use a custom format to format the number into what I wanted. But first I think you need to change the text string to a value by surrounding it with the Value formula so you end up with =VALUE(LEFT(B9,LEN(B9)-1)) Then with a custom format - select Format, Cells then Custom at the bottom of the available list. Type in 000-000-0000 in the box below the word Type on the right hand side - this should then format your numbers appropriately. Remember to format all the cells which will contain your social security numbers. Alternatively you can use a mix of Left() mid() and Right() functions combined together with &"-"& R -- Ruthki ------------------------------------------------------------------------ Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503 View this thread: http://www.excelforum.com/showthread...hreadid=392746 |
#6
|
|||
|
|||
Exactly what I needed! :-)
Thank you so much Ruthki. Hernan. "Ruthki" wrote: If all values are numbers I would use a custom format to format the number into what I wanted. But first I think you need to change the text string to a value by surrounding it with the Value formula so you end up with =VALUE(LEFT(B9,LEN(B9)-1)) Then with a custom format - select Format, Cells then Custom at the bottom of the available list. Type in 000-000-0000 in the box below the word Type on the right hand side - this should then format your numbers appropriately. Remember to format all the cells which will contain your social security numbers. Alternatively you can use a mix of Left() mid() and Right() functions combined together with &"-"& R -- Ruthki ------------------------------------------------------------------------ Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503 View this thread: http://www.excelforum.com/showthread...hreadid=392746 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Social Security Numbers & Leading Zeros | Excel Discussion (Misc queries) | |||
Social Security Number format | Excel Worksheet Functions | |||
social security numbers | Excel Worksheet Functions | |||
how to sort names and social security numbers a to z | Excel Worksheet Functions | |||
social security sorting | Excel Worksheet Functions |