How to remove dashes in Social Security number

[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

 

[Jacob Skaria]

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

 

[George]

Jacob - I am using Excel 2007 and can not find Edit - Replace !

Thanks

 

[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").Replace What:="-", _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

End Sub

 

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

 

[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

 

[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

 

[Rick Rothstein]

You might find this useful... here is Microsoft's listing for the location
of what seems like every Xl2003 to XL2007 command...

http://74.125.47.132/search?q=cache...+menu+ribbon+mapping&cd=1&hl=en&ct=clnk&gl=us

 

[Jacob Skaria]

Hi George

Shortkey is Ctrl+H

 

[George]

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

Thanks

 

[Harlan Grove]

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.

 

[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

 

[Jacob Skaria]

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

=SUBSTITUTE(D1,"-",)

If this post helps click Yes

 

[George]

Where do I insert this formula ?

Thanks- Sorry for the trouble...

 

[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

 

[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