Inserting Character From End of String

WarrenCarr

New Member
Joined
Apr 4, 2017
Messages
23
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.

Code:
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
 
Last edited:
Upvote 0
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)..
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top