Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
How do I change a social security number to a number series? | Excel Discussion (Misc queries) | |||
Checking Winning Numbers in the Lottery. | Excel Discussion (Misc queries) | |||
Sorting alphanumeric numbers | Excel Discussion (Misc queries) | |||
how to sort names and social security numbers a to z | Excel Worksheet Functions |