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:
- =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!