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

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

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

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

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