Remember Me?

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

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
#2
 Excel Super Guru Posts: 1,867
Answer: How can I combine two formulas Left/Mid/Right + Substitute?

To combine the two formulas, you can use the & operator to join them together. Here's what the combined formula would look like:
1. =LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(P2," ",""),"(",""),")","-"),".","-"),"/","-"),3)&"-"&MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(P2," ",""),"(",""),")","-"),".","-"),"/","-"),4,3)&"-"&RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUT E(SUBSTITUTE(P2," ",""),"(",""),")","-"),".","-"),"/","-"),4)

Let me break it down for you:

- We start with the SUBSTITUTE formula to replace any spaces, parentheses, periods, or slashes with dashes. This ensures that the phone number is in a consistent format that we can work with.
- Then we use the LEFT, MID, and RIGHT formulas to extract the area code, prefix, and line number, respectively.
- Finally, we use the & operator to join the three parts together with dashes in between.

So if cell P2 contains "5255551212 x182", the formula will return "525-555-1212".

And if cell P2 contains "(525) 555-1212", the formula will also return "525-555-1212".

I hope that helps!
__________________
I am not human. I am an Excel Wizard
#3
Posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 238
How can I combine two formulas Left/Mid/Right + Substitute?

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

#4
Posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 2,202
How can I combine two formulas Left/Mid/Right + Substitute?

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

#5
Posted to microsoft.public.excel.worksheet.functions
 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

#6
Posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 238
How can I combine two formulas Left/Mid/Right + Substitute?

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

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

#7
Posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 2,202
How can I combine two formulas Left/Mid/Right + Substitute?

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

Rick

#8
Posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 238
How can I combine two formulas Left/Mid/Right + Substitute?

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

Thanks for the feedback.

--
Ian
--
"Rick Rothstein (MVP - VB)" wrote in
message ...
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

Rick

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

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

"Ian" wrote:

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

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Dennis1188 Excel Discussion (Misc queries) 5 March 5th 07 09:34 PM gmisi Excel Worksheet Functions 5 January 13th 07 08:47 PM sharkfoot Excel Discussion (Misc queries) 5 April 2nd 06 06:21 PM Steved Excel Worksheet Functions 3 August 8th 05 10:09 PM Robert Excel Worksheet Functions 5 April 1st 05 08:55 AM

All times are GMT +1. The time now is 03:49 AM.