Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Unformating social security numbers


I export data from Quickbooks to excel, then import into access. Having
had problems with duplicate social security numbers and access was not
catching it.
The data in Quickbooks shows a social security number as 000-00-0000,
but it is not required to put the dashes and it has happend that an
employee is listed twice in access, once with and once without the
dashes.

To avoid this, I am trying to eliminate the dashes in excel before
importing into access.
We the data is exported from Quickbooks, I have tried to format the
social security column as text, as well as general, but when I use Find
and Replace to get rid of the dashes, it loosing the zeros of the first
character.
Any suggestions?
Anne


--
annep
------------------------------------------------------------------------
annep's Profile: http://www.excelforum.com/member.php...o&userid=18851
View this thread: http://www.excelforum.com/showthread...hreadid=568622

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default Unformating social security numbers

Use an apostrophe (single quote) for example in A1 enter:
'000-00-0000

format A2 as Text and enter:
000-00-0000

They will look the same, but if you use find/replace to remove the dash, A1
will retain all the leading zeros and A2 will not.
--
Gary's Student


"annep" wrote:


I export data from Quickbooks to excel, then import into access. Having
had problems with duplicate social security numbers and access was not
catching it.
The data in Quickbooks shows a social security number as 000-00-0000,
but it is not required to put the dashes and it has happend that an
employee is listed twice in access, once with and once without the
dashes.

To avoid this, I am trying to eliminate the dashes in excel before
importing into access.
We the data is exported from Quickbooks, I have tried to format the
social security column as text, as well as general, but when I use Find
and Replace to get rid of the dashes, it loosing the zeros of the first
character.
Any suggestions?
Anne


--
annep
------------------------------------------------------------------------
annep's Profile: http://www.excelforum.com/member.php...o&userid=18851
View this thread: http://www.excelforum.com/showthread...hreadid=568622


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Unformating social security numbers


Found the answer from Dave Peterson's reply on 7/17/06 to a similar
question

If the hyphens are part of the number format (not part of the value),
then
Select the range
format|cells|Number tab
custom category
type: 000000000
in the "Type:" box

If the hyphens are really part of the data (liked they were typed in),
then
Select the range
edit|replace
what: - (hyphen)
with: (leave blank)
replace all


--
annep
------------------------------------------------------------------------
annep's Profile: http://www.excelforum.com/member.php...o&userid=18851
View this thread: http://www.excelforum.com/showthread...hreadid=568622

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Unformating social security numbers


Actually did not solve the problem. Now looking at the individual
socials, although the display shows 023456789, when I look at the cell
in edit mode,
if only shows 23456789, which does not help because I need to import
this data into access and the leading zero is missing.

putting the ' in front of the number, would solve the problem, but I
don't know how to automate that. with 524 employees, I can't do that in
edit mode.
Anne


--
annep
------------------------------------------------------------------------
annep's Profile: http://www.excelforum.com/member.php...o&userid=18851
View this thread: http://www.excelforum.com/showthread...hreadid=568622

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Unformating social security numbers

Convert the column to real numbers (remove those hyphens).

You could use a helper column filled with formulas:

=text(a1,rept("0",9))
and drag down.

And then select this column
edit|copy
edit|paste special|values
Now that helper column is text with the leading 0's.


Delete the original column if you want.





annep wrote:

Actually did not solve the problem. Now looking at the individual
socials, although the display shows 023456789, when I look at the cell
in edit mode,
if only shows 23456789, which does not help because I need to import
this data into access and the leading zero is missing.

putting the ' in front of the number, would solve the problem, but I
don't know how to automate that. with 524 employees, I can't do that in
edit mode.
Anne

--
annep
------------------------------------------------------------------------
annep's Profile: http://www.excelforum.com/member.php...o&userid=18851
View this thread: http://www.excelforum.com/showthread...hreadid=568622


--

Dave Peterson
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
Seed numbers for random number generation, uniform distribution darebo Excel Discussion (Misc queries) 3 April 21st 23 09:02 PM
How do I change a social security number to a number series? LCDawn Excel Discussion (Misc queries) 4 December 22nd 05 05:15 PM
Checking Winning Numbers in the Lottery. Ann Excel Discussion (Misc queries) 4 May 18th 05 10:55 AM
Sorting alphanumeric numbers maurice.centner Excel Discussion (Misc queries) 2 May 6th 05 02:00 AM
how to sort names and social security numbers a to z olajune1 Excel Worksheet Functions 2 February 25th 05 01:31 PM


All times are GMT +1. The time now is 02:19 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"