Inserting Character From End of String

[WarrenCarr]

I need a way to insert a "." into a string 3 characters from the end. I have a list of emails but they do not have the period.

Example

someone@worldwidewebcom - Needs to add "." before the com

There is a long list so either a formula or VBA is needed.

Thanks,
WC

 

[Rick Rothstein]

Here is a formula...

=REPLACE(A1,LEN(A1)-2,0,".")

 

[DanteAmor]

If you want the result in the same column, we can use Rick's formula in the following macro.
The code assumes that the data begins in row 2.

Sub Add_Dot()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate("=IF({1},REPLACE(" & .Address & ",LEN(" & .Address & ")-2,0,"".""))")
  End With
End Sub

 

[pella88]

Here are two more solutions, even though I agree that Rick's solution is best.

=LEFT(A1,LEN(A1)-3)&"."&RIGHT(A1,3) - works the same, just a bit more "complicated"

=SUBSTITUTE(A1,"com",".com") - this one works the same as CTRL+H (find and replace), with a shortcoming that in case that there are multiple occurrences of "com", all of them will be replaced (e.g: someonecom@worldwidewebcom would end as someone.com@worldwideweb.com). Of course, this can also be solved with a bit of more "complication":

=SUBSTITUTE(A1,"com",".com",(LEN(A1)-LEN(SUBSTITUTE(A1,"com","")))/3)

Of course all options are viable, and is mostly personal choice (except second option in my post, which will not work in all cases)..