Cell content / formula bar different

  • Thread starter Thread starter philtyler
  • Start date Start date
P

philtyler

I imported a database from !ACT to excel which included a phone# column

I need to sort the column but many of the phone numbers have the area
code in parentheses (207)-555-1234 so they sort seperately from
207-555-1234.

The problem is when I try to edit the phone# in the cell the ( )
disappears. They aren't in the formula bar either so I can't fix the
problem.

Formatting the whole column as phone numbers doesn't help.

I could retype them all but there are thousands.

Any suggestions why the cell would display and sort one way yet
actually contain something different when selected?
 
Consider changing all values that look like:
(207)-555-1234
into:
2075551234

Then sorting will work normally.


First enter this tiny UDF:

Function dephone(r As Range) As Long
Dim s As String
s = r.Value
s = Replace(s, "(", "")
s = Replace(s, ")", "")
s = Replace(s, "-", "")
dephone = --s
End Function

If your phone numbers are in column A, then in an un-used column enter:

=dephone(A1) and copy down.

Copy this new dephone column and paste as value elsewhere and you are ready
for sorting.
 
Back
Top