+ Reply to Thread
Results 1 to 16 of 16

Find and Replace trailing characters

  1. 12-21-2022, 01:25 PM #1
    kjxavier
    kjxavier is offline
    Registered User
    Join Date
    07-20-2011
    Location
    Kerala, India
    MS-Off Ver
    Excel 2013
    Posts
    93

    Find and Replace trailing characters

    Hi!
    I am using Windows 10 with Office Plus 2010

    I have bulk email ID's in an excel sheet column A, but I have some email Id's spelled wrong (eg: .com is spelled as .comu or .comi)
    I wish to remove all trailing character that appear after the word ".com"

    I appreciate your early help.

    Thank you
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-21-2022, 01:31 PM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Find and Replace trailing characters

    One way:


    =SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",50)),50)),LEFT(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",50)),50)),3))
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 12-21-2022, 01:33 PM #3
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,372

    Re: Find and Replace trailing characters

    For your sample data:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  4. 12-21-2022, 01:39 PM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Find and Replace trailing characters

    yes... that was my first go. But then I noticed the .edu. If any of them come back as .edui

    I don't like mine much, though...
    Reply With QuoteRegister To Reply
  5. 12-21-2022, 01:45 PM #5
    kjxavier
    kjxavier is offline
    Registered User
    Join Date
    07-20-2011
    Location
    Kerala, India
    MS-Off Ver
    Excel 2013
    Posts
    93

    Re: Find and Replace trailing characters

    Perfect, thanks a lot
    Reply With QuoteRegister To Reply
  6. 12-21-2022, 01:47 PM #6
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,372

    Re: Find and Replace trailing characters

    Ahh, missed that:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 12-21-2022, 01:49 PM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Find and Replace trailing characters

    That's the one, Trevor!!
    Reply With QuoteRegister To Reply
  8. 12-21-2022, 01:53 PM #8
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,372

    Re: Find and Replace trailing characters

    @Glenn: you must take the credit for a solution that copes with multiple trailing characters, for example, .comix
    Reply With QuoteRegister To Reply
  9. 12-21-2022, 01:54 PM #9
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Find and Replace trailing characters

    LoL. I was too early congratulating you...
    Reply With QuoteRegister To Reply
  10. 12-21-2022, 02:15 PM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Find and Replace trailing characters

    My last effort was ugly, so I persisted... This is prettier, and a little shorter...

    =LEFT(A1,3+MAX(ISNUMBER(SEARCH(".",MID(A1,ROW($1:$100),1)))*ROW($1:$100)))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 12-21-2022, 02:24 PM #11
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,152

    Re: Find and Replace trailing characters

    Or

    =IFERROR(LEFT(A1,FIND(".com",A1,1))&"com",A1)1)
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  12. 12-21-2022, 02:25 PM #12
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Find and Replace trailing characters

    JT. See Post 4.
    Reply With QuoteRegister To Reply
  13. 12-21-2022, 02:26 PM #13
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,704

    Re: Find and Replace trailing characters

    How about this in B1:

    =LEFT(A1,FIND(".",A1,FIND("@",A1)))&MID(A1,FIND(".",A1,FIND("@",A1))+1,3)

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  14. 12-21-2022, 02:30 PM #14
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,152

    Re: Find and Replace trailing characters

    EDU comes back as EDU

    I get same results as TMS!
    Reply With QuoteRegister To Reply
  15. 12-21-2022, 02:51 PM #15
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Find and Replace trailing characters

    Yes, but what would edui or eduic come back as?
    Reply With QuoteRegister To Reply
  16. 12-22-2022, 02:18 AM #16
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,372

    Re: Find and Replace trailing characters

    @kjxavier: Thanks for the rep.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find and Replace Macro to replace list of special characters
    By dcowiesmith in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-22-2021, 02:01 PM
  2. Best way to find/replace Unicode characters
    By greek in forum Word Programming / VBA / Macros
    Replies: 4
    Last Post: 03-17-2014, 05:39 PM
  3. [SOLVED] find and replace to not replace characters found as wildcards
    By sabutler4 in forum Excel General
    Replies: 4
    Last Post: 07-03-2013, 06:48 PM
  4. Find and replace characters.
    By Cyberpawz in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-04-2012, 02:31 PM
  5. [SOLVED] Find and replace - max characters per row
    By dshilan in forum Excel General
    Replies: 1
    Last Post: 04-03-2012, 03:09 PM
  6. find and replace certain characters
    By imichalopo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-31-2010, 03:08 PM
  7. Find and Replace Characters
    By lytaylor in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-15-2005, 09:03 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1