Unformating social security numbers

[annep]

I export data from Quickbooks to excel, then import into access. Havin
had problems with duplicate social security numbers and access was no
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 a
employee is listed twice in access, once with and once without th
dashes.

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

 

[Guest]

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.

 

[annep]

Found the answer from Dave Peterson's reply on 7/17/06 to a simila
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 al

 

[annep]

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

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

 

[Dave Peterson]

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.