ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Cell content / formula bar different (https://www.excelbanter.com/excel-discussion-misc-queries/87061-cell-content-formula-bar-different.html)

philtyler

Cell content / formula bar different
 

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?


--
philtyler
------------------------------------------------------------------------
philtyler's Profile: http://www.excelforum.com/member.php...o&userid=12476
View this thread: http://www.excelforum.com/showthread...hreadid=539315


Gary''s Student

Cell content / formula bar different
 
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.
--
Gary's Student


"philtyler" wrote:


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?


--
philtyler
------------------------------------------------------------------------
philtyler's Profile: http://www.excelforum.com/member.php...o&userid=12476
View this thread: http://www.excelforum.com/showthread...hreadid=539315




All times are GMT +1. The time now is 12:19 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com