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

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
>
>

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

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

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