![]() |
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 |
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