getting rif of apostrophe in front of ss#

  • Thread starter Thread starter jrh
  • Start date Start date
J

jrh

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
 
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
 
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.
 
Back
Top