Replace Formatted Cell Contents

[Guest]

I have some mixed data formatting in a column, SSN. Some of it is in the
format Numeric, then formatted 000-00-0000, and some of it is in the Format
"123-45-6789". I need to make it all alike -- how do I proceed?

 

[Guest]

Sorry, not too clear. Try again:

Some cells contain data like 13640681, formatted Special, SSN. Some files
contain data like 013-64-0681. These variants may occur for the same
individual. I need to sort the data to bring all of any individual's records
together, hence I need to convert all of one kind or the other to a format
that makes them all formatted the same.

I hope that's a bit clearer!

 

[Guest]

This macro will convert a group of mixed text and numeric SSN# into pure text:

Sub social()
Dim s As String
For Each r In Selection
s = r.Text
r.Clear
r.NumberFormat = "@"
r.Value = s
Next
End Sub


just Select & run.

 

[Guest]

<Some files > -- of course I meant "Some CELLS" (sheesh, getting too old for
this <g>!)

 

[Guest]

Magnificent -- worked like a bleedin' charm!

 

[Guest]

You are welcome. This is, unfortunately, a common real-world problem.

 

[Guest]

So, here it is over 2 months later, and I think it is time for me to expand
my "Thanks". I now have a hidden WB called "Public Macros", and a slightly
modified version of your "Social" macro is the most frequently used member of
that file's citizens. Here is my modest little tweak:

Public Sub Social()
'
' Social Macro
'
Dim s As String
For Each R In Range("C3", Range("C3").End(xlDown))
With R
s = .Text
z = .Font.Size
c = .Interior.ColorIndex
.Clear
.NumberFormat = "000-00-0000"
.Font.Size = z
.Value = s
With .Interior
.ColorIndex = c
.Pattern = xlSolid
End With
End With
Next

This macro has made cross-WB searching a whole gang better! Cannot thank you
enough.
End Sub