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

Select the column.From menu EditReplaceFindwhat put the dash.
Replace with 'leave blank. and OK
--
If this post helps click Yes
---------------
Jacob Skaria


"George" wrote:

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 - I am using Excel 2007 and can not find Edit - Replace !

Thanks

"Jacob Skaria" wrote:

Select the column.From menu EditReplaceFindwhat put the dash.
Replace with 'leave blank. and OK
--
If this post helps click Yes
---------------
Jacob Skaria


"George" wrote:

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

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


"George" wrote:

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

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.

--
Rick (MVP - Excel)


"George" wrote in message
...
Jacob - I am using Excel 2007 and can not find Edit - Replace !

Thanks

"Jacob Skaria" wrote:

Select the column.From menu EditReplaceFindwhat put the dash.
Replace with 'leave blank. and OK
--
If this post helps click Yes
---------------
Jacob Skaria


"George" wrote:

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

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



"john" wrote:

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


"George" wrote:

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

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

"George" wrote:

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



"john" wrote:

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


"George" wrote:

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

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:...&ct=clnk&gl=us

--
Rick (MVP - Excel)


"George" wrote in message
...
Jacob - I am using Excel 2007 and can not find Edit - Replace !

Thanks

"Jacob Skaria" wrote:

Select the column.From menu EditReplaceFindwhat put the dash.
Replace with 'leave blank. and OK
--
If this post helps click Yes
---------------
Jacob Skaria


"George" wrote:

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

Hi George

Shortkey is Ctrl+H
--
If this post helps click Yes
---------------
Jacob Skaria


"George" wrote:

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

Thanks

"Jacob Skaria" wrote:

Select the column.From menu EditReplaceFindwhat put the dash.
Replace with 'leave blank. and OK
--
If this post helps click Yes
---------------
Jacob Skaria


"George" wrote:

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

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

Thanks


"Eduardo" wrote:

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

"George" wrote:

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



"john" wrote:

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


"George" wrote:

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

George wrote...
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.

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

"George" wrote:

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

Thanks


"Eduardo" wrote:

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

"George" wrote:

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



"john" wrote:

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


"George" wrote:

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

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

=SUBSTITUTE(D1,"-",)

If this post helps click Yes
---------------
Jacob Skaria


"George" wrote:

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

Thanks


"Eduardo" wrote:

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

"George" wrote:

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



"john" wrote:

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


"George" wrote:

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

Where do I insert this formula ?

Thanks- Sorry for the trouble...

"Jacob Skaria" wrote:

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

=SUBSTITUTE(D1,"-",)

If this post helps click Yes
---------------
Jacob Skaria


"George" wrote:

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

Thanks


"Eduardo" wrote:

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

"George" wrote:

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



"john" wrote:

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


"George" wrote:

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

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


"George" wrote:

Where do I insert this formula ?

Thanks- Sorry for the trouble...

"Jacob Skaria" wrote:

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

=SUBSTITUTE(D1,"-",)

If this post helps click Yes
---------------
Jacob Skaria


"George" wrote:

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

Thanks


"Eduardo" wrote:

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

"George" wrote:

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



"john" wrote:

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


"George" wrote:

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

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
"George" wrote:

Where do I insert this formula ?

Thanks- Sorry for the trouble...

"Jacob Skaria" wrote:

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

=SUBSTITUTE(D1,"-",)

If this post helps click Yes
---------------
Jacob Skaria


"George" wrote:

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

Thanks


"Eduardo" wrote:

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

"George" wrote:

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



"john" wrote:

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


"George" wrote:

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