How can I combine two formulas Left/Mid/Right + Substitute?

[SCrowley]

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(SUBSTITUTE(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.

 

[Ian]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[SCrowley]

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

scrowley(AT)littleonline.com

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

 

[Ian]

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
--

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

 

[Rick Rothstein \(MVP - VB\)]

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

Don't get me wrong, I wasn't suggesting that your code was inelegant or that
my code was in some way "better" than yours; rather, I just though you (and
the readers of this thread) might find the little used (known?) user-defined
character formatting ability for the Format command of some interest.

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

Since my code is only modifying a small part of your code, it will do
exactly what your code does.

Rick

 

[Ian]

No problem, Rick. I wasn't offended, and I'm always willing to learn
different & easier ways.

Thanks for the feedback.

 

[SCrowley]

Ian,

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

I just need all the phone numbers to have a uniform format. It does not
change the data.

Thank you both, again, for all of your help!!!



--
Thank you,

scrowley(AT)littleonline.com

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
--

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