Results 1 to 4 of 4

Thread: Inserting Character From End of String
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Inserting Character From End of String

    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

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,244
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Inserting Character From End of String

    Here is a formula...

    =REPLACE(A1,LEN(A1)-2,0,".")
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,257
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Inserting Character From End of String

    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 by DanteAmor; Sep 20th, 2019 at 05:29 PM.
    Regards Dante Amor

  4. #4
    Board Regular
    Join Date
    Aug 2013
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Inserting Character From End of String

    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 by pella88; Sep 20th, 2019 at 05:41 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •