Concatenate question to remove a period (everything but this already figured out)

[thisdumbdot]

Hey everyone,

I just stumbled across this forum during a frenzied state of Google searches, but with virtually zero experience with Excel I was fortunate enough to reverse engineer 99% of a phone number formatting issue.

The problem is I can't figure out how to remove the first "." from "555.555.5555" to change it to "+15555555555"

Here's the formula I have so far: =concatenate("+1",left(N2,3),"",mid(N2,4,3),"",right(N2,4))

But I can't seem to identify where the "." is still carrying over from?

Here's what happens:

Column N Column O
555.555.5555 +1555.5555555

Any ideas/solutions would be greatly appreciated!!

 

[Fluff]

Hi & welcome to MrExcel.
How about
="+1"&SUBSTITUTE(N2,".","")

 

[Finalfight40]

Its from your mid function i believe. You can change it to:

=concatenate("+1",left(N2,3),"",mid(N2,5,3),"",right(N2,4))

but i would just use:

="+1" & SUBSTITUTE(N2, ".", "")

 

[thisdumbdot]

Awesome, thank you both so much!

 

[Fluff]

Glad we could help & thanks for the feedback