View Single Post
#5
Posted to microsoft.public.excel.worksheet.functions
 SCrowley external usenet poster Posts: 47
How can I combine two formulas Left/Mid/Right + Substitute?

Thank you BOTH. It worked beautifully!!!!
--

scrowley(AT)littleonline.com

"Rick Rothstein (MVP - VB)" wrote:

I thought you might find it of some interest that the If-Then-ElseIf section

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