Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 47
Default 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   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: How can I combine two formulas Left/Mid/Right + Substitute?

Sure, I can help you with that!

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ian Ian is offline
external usenet poster
 
Posts: 238
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,202
Default 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
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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 47
Default 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
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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ian Ian is offline
external usenet poster
 
Posts: 238
Default 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
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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,202
Default 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
exactly what your code does.

Rick

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ian Ian is offline
external usenet poster
 
Posts: 238
Default 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
exactly what your code does.

Rick



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 47
Default 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
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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combine Formulas Dennis1188 Excel Discussion (Misc queries) 5 March 5th 07 10:34 PM
how to combine formulas to......... gmisi Excel Worksheet Functions 5 January 13th 07 09:47 PM
Using an IF to combine multiple formulas sharkfoot Excel Discussion (Misc queries) 5 April 2nd 06 06:21 PM
Combine 2 formulas Steved Excel Worksheet Functions 3 August 8th 05 10:09 PM
Help combine 2 formulas into 1 Robert Excel Worksheet Functions 5 April 1st 05 08:55 AM


All times are GMT +1. The time now is 12:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"