I suggested my solution wasn't the most elegant :-) Thanks for the pointer.

As a matter of interest, is your code changing the layout of the data in the
cell, or just applying a format?

--
Ian
--
"Rick Rothstein (MVP - VB)" wrote in
message ...
I thought you might find it of some interest that the If-Then-ElseIf
section of your code...

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

can be simplified to this...

If Len(strout) = 10 Then
strout = Format(strout, "@@@-@@@-@@@@ ")
ElseIf strout < "" Then
' In any other case, prefix number with ?
strout = "?" & strout
End If

provided you make this one minor change in the code that parses the cell
value to digits only (we need to leave the "X" in the string if there is
one). So, this line inside your For-Next loop...

If IsNumeric(Mid(strin, charnum, 1)) = True Then

needs to become this line instead....

If IsNumeric(Mid(strin, charnum, 1)) = True Or _
Mid(strin, charnum, 1) Like "[xX]" Then

Rick