How to remove dashes in Social Security number

G

George

Each month I receive an excel spreadsheet and one of the columns has social
security numbers with the dashes. I have to import it into Access without the
dashes, how can I remove the dashes from the social security numbers in Excel
- without doing it by hand ? Is there a way to use format cells to clear the
dashes ?

Thanks
 
J

Jacob Skaria

Select the column.From menu Edit>Replace>Findwhat put the dash.
Replace with 'leave blank. and OK
 
J

john

Jacobs’s method easiest but if you want macro following should work.
I have used Column D but you will need to amend as required


Sub ReplaceDashes()


Columns("D:D").Replace What:="-", _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

End Sub
 
R

Rick Rothstein

For future questions you ask on these newsgroups, you should make sure to
mention the version of Excel you are using so that you get answers that
apply to your situation.

The Edit/Replace can be found on the Home tab, Editing panel, Find&Select
drop down.
 
G

George

John - That works but it drops any leading zeros,
plus I am in the Air Force and they don't like Macros
running on their system without them proofing them.

How can I get Jacob's suggestion to work ?

Thanks
 
E

Eduardo

Hi George,
Highlight the column where you have the numbers
Press CTRL H, find what put the dash, replace with leave blank, replace all
 
H

Harlan Grove

George said:
That works but it drops any leading zeros from SS# that start with zero !
....

You're importing this into Access. If the Access field type is text
spanning 9 characters, then in Excel the first step you need to
perform is applying the number format Text (that's its name) to the
column of SSNs, then [Ctrl]+H to display the Replace dialog, then
follow the other respondents' suggestions. If the Access field type is
number formatted with leading zeros, then there shouldn't be a problem
with Excel dropping the leading zeros.
 
E

Eduardo

Hi George,
we need to work around, add a column and use this formula

=TEXT(A1,"000000000")

the zeros are all the digits in the social insurance #, I consider 9, if
they are more just add it to the formula, it will solve your problem

change A1 to where your range start and copy formula down
 
J

Jacob Skaria

Insert a column and use the below formula. Copy down as required

=SUBSTITUTE(D1,"-",)

If this post helps click Yes
 
J

Jacob Skaria

Suppose you have the SS# in ColA. Insert a column between ColA and ColB. Then
enter this formula in ColA cell 1

=SUBSTITUTE(A1,"-",)

If this post helps click Yes
 
E

Eduardo

Hi,
you have to put it in an adjacent column, you can use subtitute or

=TEXT(A1,"000000000")

as explained before

if your data start in cell A1 you put the formula in B1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top