I am trying to compare two lists by social security
number. However, one list has an apostrophe in front of
the ss# so the vlookup doesn't work. how do i get rid of
the apostrophe and make the format the same as the other
ss#?

thank you

Make a backup of your data before trying this....

Put this in a standard module:

Sub ElimApost()
Dim c As Range, f As String
If Not TypeOf Selection Is Range Then Exit Sub
For Each c In Selection
If c.PrefixCharacter = "'" Then c.Formula = c.Value
Next c
End Sub

Then highlight your ss#'s A10:A500

Tools, Macro, select ElimApost, and Run






"jrh" wrote in message
...
I am trying to compare two lists by social security
number. However, one list has an apostrophe in front of
the ss# so the vlookup doesn't work. how do i get rid of
the apostrophe and make the format the same as the other
ss#?

thank you

Thanks, I guess the format also includes dashes in between
the numbers like a normal social security number has but
the data in the cell shouldn't actually have the dashes it
should just appear that way with the ss# formatting. Can
I somehow eliminate the dashes from the text and then
format the remaining numbers with SS# format?

thank you
-----Original Message-----
Make a backup of your data before trying this....

Put this in a standard module:

Sub ElimApost()
Dim c As Range, f As String
If Not TypeOf Selection Is Range Then Exit Sub
For Each c In Selection
If c.PrefixCharacter = "'" Then c.Formula = c.Value
Next c
End Sub

Then highlight your ss#'s A10:A500

Tools, Macro, select ElimApost, and Run






"jrh" wrote in
message
...
I am trying to compare two lists by social security
number. However, one list has an apostrophe in front of
the ss# so the vlookup doesn't work. how do i get rid
of
the apostrophe and make the format the same as the other
ss#?

thank you


.

Instead of the VBA code, you could create a helper column (a clean empty
column to the right (Maybe a quick insert of one)) -- If your ss#'s are in
A5:A500 as '111-22-3333
in B5 enter =SUBSTITUTE(A5,"-","") and copy down to B500. This will
eliminate the dashes and the leading " ' ". At present B5:B500 is a
formula; highlight B5:B500 and Copy, then (without moving do an Edit
Paste-Special Value, OK Now B5:B500 are
Values -- Move them to A5:A500.


wrote in message
...
Thanks, I guess the format also includes dashes in between
the numbers like a normal social security number has but
the data in the cell shouldn't actually have the dashes it
should just appear that way with the ss# formatting. Can
I somehow eliminate the dashes from the text and then
format the remaining numbers with SS# format?

thank you
-----Original Message-----
Make a backup of your data before trying this....

Put this in a standard module:

Sub ElimApost()
Dim c As Range, f As String
If Not TypeOf Selection Is Range Then Exit Sub
For Each c In Selection
If c.PrefixCharacter = "'" Then c.Formula = c.Value
Next c
End Sub

Then highlight your ss#'s A10:A500

Tools, Macro, select ElimApost, and Run






"jrh" wrote in
message
...
I am trying to compare two lists by social security
number. However, one list has an apostrophe in front of
the ss# so the vlookup doesn't work. how do i get rid
of
the apostrophe and make the format the same as the other
ss#?

thank you


.

use Edit=Replace

replace hyphens with nothing

the format the cells as you say.

--
Regards,
Tom Ogilvy

wrote in message
...
Thanks, I guess the format also includes dashes in between
the numbers like a normal social security number has but
the data in the cell shouldn't actually have the dashes it
should just appear that way with the ss# formatting. Can
I somehow eliminate the dashes from the text and then
format the remaining numbers with SS# format?

thank you
-----Original Message-----
Make a backup of your data before trying this....

Put this in a standard module:

Sub ElimApost()
Dim c As Range, f As String
If Not TypeOf Selection Is Range Then Exit Sub
For Each c In Selection
If c.PrefixCharacter = "'" Then c.Formula = c.Value
Next c
End Sub

Then highlight your ss#'s A10:A500

Tools, Macro, select ElimApost, and Run






"jrh" wrote in
message
...
I am trying to compare two lists by social security
number. However, one list has an apostrophe in front of
the ss# so the vlookup doesn't work. how do i get rid
of
the apostrophe and make the format the same as the other
ss#?

thank you


.