Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Hernan
 
Posts: n/a
Default 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   Report Post  
Ruthki
 
Posts: n/a
Default


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   Report Post  
Hernan
 
Posts: n/a
Default

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   Report Post  
Ruthki
 
Posts: n/a
Default


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   Report Post  
Hernan
 
Posts: n/a
Default

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   Report Post  
Hernan
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Social Security Numbers & Leading Zeros CSS Excel Discussion (Misc queries) 2 June 24th 05 12:23 AM
Social Security Number format Jean Excel Worksheet Functions 1 March 7th 05 10:37 PM
social security numbers Jean Excel Worksheet Functions 4 March 7th 05 08:37 PM
how to sort names and social security numbers a to z olajune1 Excel Worksheet Functions 2 February 25th 05 01:31 PM
social security sorting Precious Pearl Excel Worksheet Functions 4 January 25th 05 03:37 PM


All times are GMT +1. The time now is 07:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"