I don't know how you can do it with a formula, but it can be done with code.

I'm assuming you have a series of entries, and I'm guessing they're in
column P
I'm assuming that the numeric part of the number should be 10 digits plus an
optional extension number

This probably isn't the most elegant way, but it seems to work. To be safe,
make a copy of your data before trying this out.

Private Sub test()
For rownum = 2 To 100 ' Range of rows to be processed
strin = Range("P" & rownum).Text ' I'm assuming column P
strout = ""
For charnum = 1 To Len(strin)
If IsNumeric(Mid(strin, charnum, 1)) = True Then
' If the character is a number, add it to the output string
strout = strout & Mid(strin, charnum, 1)
End If
Next charnum
If Len(strout) 10 Then
' If there's an extension number included
strout = Left(strout, 3) & "-" & Mid(strout, 4, 3) & "-" & Mid(strout, 7, 4)
& "x" & Right(strout, Len(strout) - 10)
ElseIf Len(strout) = 10 Then
' If it's a straight 10 digit number
strout = Left(strout, 3) & "-" & Mid(strout, 4, 3) & "-" & Mid(strout, 7, 4)
ElseIf Len(strout) = 0 Then
' If there were no numbers in the original data then the result will be a
blank cell
Else
' In any other case, prefix number with ?
strout = "?" & strout
End If
' Assuming you want to overwrite the original data.
' If not, change P to another column
Range("P" & rownum).Value = strout

Next rownum
End Sub


--
Ian
--
"SCrowley" wrote in message
...
Excel 2007 - records with varying formats of phone numbers+text.

I would like to combine =LEFT(P2,3)&"-"&MID(P2,4,3)&"-"&RIGHT(P5,4) to
separate any numbers entered as 5555551212 AND/OR
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(P2,"
",""),"(",""),")","-"),".","-"),"/","-")

So that Cell P2 (current contents are 5255551212 x182) will result as
525-555-1212 x 182 AND/OR to substitute any numbers entered as (525)
555-1212, 525.555.1212, etc.

--
Thank you,

scrowley(AT)littleonline.com