| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
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:
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combine Formulas | Excel Discussion (Misc queries) | |||
| how to combine formulas to......... | Excel Worksheet Functions | |||
| Using an IF to combine multiple formulas | Excel Discussion (Misc queries) | |||
| Combine 2 formulas | Excel Worksheet Functions | |||
| Help combine 2 formulas into 1 | Excel Worksheet Functions | |||
Linear Mode
