+ Reply to Thread
Results 1 to 15 of 15

Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Approach

Hybrid View

  1. 09-14-2022, 06:20 AM #1
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,522

    Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Approach 

    Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Approach
    Dear Gurus,

    In some scenarios, we have to extract data from Multiple Tools and Websites where the Mobile No comes with trailing spaces or spaces after 4 Digits or even "+91" Indian STD Code or Dash "-", so Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING.

    Ex -
    1] +91 9820 123 456 needs to be replaced by simply 920123456
    2] 98201 23456 - 9820123456
    3] 9820123456 / 57 - 920123456 & 9820123457

    Is there a possibily of using some iterative logic to iterate between all the characters which need to be replaced - maybe like OR - ROW(INDIRECT("1:100") Combination which I am not an expert in...

    Regards
    e4excel
    Reply With QuoteRegister To Reply
  2. 09-14-2022, 06:47 AM #2
    nick.williams
    nick.williams is offline
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Appro

    I don't know of a way in Excel 2016 (without using nested REPLACE or SUBSTITUTE functions). In 365 you could do it with an iterative LAMBDA function I think.
    Reply With QuoteRegister To Reply
  3. 09-14-2022, 06:53 AM #3
    sweep
    sweep is offline
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,454

    Re: Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Appro

    Hi,

    THis should do what you need. In 2016, you may have to enter as an array (CRTL, SHIFT & ENTER).

    Formula: copy to clipboard 
    =TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),""))
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
    Reply With QuoteRegister To Reply
  4. 09-15-2022, 01:07 AM #4
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,522

    Re: Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Appro

    Dear Sweep & Nick,

    Thanks for the help however just recently my office has downgraded the Office to 2010 so is there any other option which can be made use of...
    Reply With QuoteRegister To Reply
  5. 09-14-2022, 06:58 AM #5
    nick.williams
    nick.williams is offline
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Appro

    TEXTJOIN isn't available in 2016 (2019 onwards).
    Reply With QuoteRegister To Reply
  6. 09-15-2022, 01:37 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,998

    Re: Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Appro

    Please change your forum profile to reflect the Office version you are using.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  7. 09-15-2022, 03:38 AM #7
    nick.williams
    nick.williams is offline
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Appro

    Why can't you use nesting? Is it because you have a large number of different characters to remove?
    Reply With QuoteRegister To Reply
  8. 09-15-2022, 05:34 AM #8
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,522

    Re: Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Appro

    Yes Lot of characters so I thought something else than nesting would be more useful
    Reply With QuoteRegister To Reply
  9. 09-15-2022, 05:40 AM #9
    menem
    menem is offline
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Appro

    Also exclude helper cells ?

    Regards.
    My English is very poor, so please be patient >_<"

    My Dynamic Related Dropdown post:
    https://www.excelforum.com/excel-for...ml#post5657376
    https://www.excelforum.com/excel-for...ml#post5655711
    https://www.excelforum.com/excel-for...ml#post5655762
    Reply With QuoteRegister To Reply
  10. 09-15-2022, 07:36 AM #10
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,522

    Re: Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Appro

    Yes Excluding would be better...
    Last edited by AliGW; 09-15-2022 at 07:38 AM. Reason: Please DON'T quote unnecessarily!
    Reply With QuoteRegister To Reply
  11. 09-15-2022, 07:46 AM #11
    Pete_UK
    Pete_UK is online now
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,423

    Re: Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Appro

    Attach a sample Excel workbook which shows examples of all the variations you want to cope with. Your first post just gives 3 examples, which are easy to deal with using a few nested functions.

    Pete
    Reply With QuoteRegister To Reply
  12. 09-16-2022, 03:42 AM #12
    menem
    menem is offline
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Appro

    Seem I can only deal with 2nd sample, for 1st and 3rd can't. T_T

    Regards.
    Reply With QuoteRegister To Reply
  13. 09-16-2022, 03:48 AM #13
    nick.williams
    nick.williams is offline
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Appro

    Is VBA an option or does it have to be a formula?
    Reply With QuoteRegister To Reply
  14. 09-16-2022, 03:53 AM #14
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,998

    Re: Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Appro

    Based just on what we have been given, try this:

    =IFERROR(IF(FIND(" / ",A1)>0,LEFT(A1,10)&" & "&LEFT(A1,8)&RIGHT(A1,2)),RIGHT(SUBSTITUTE(A1," ",""),10))

    Although I suspect the real data is far more complex. We shall see!
    Reply With QuoteRegister To Reply
  15. 09-16-2022, 05:41 AM #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 2406
    Posts
    44,662

    Re: Is it possible to REPLACE Multiple Characters with NOTHING without using NESTING Appro

    Yes - we need a REPRESENTATIVE Excel sheet, not just 3 examples. Nesting may be your best (only) option
    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
    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] Enhance (accelerate) code to replace multiple special characters in large dataset
    By mamaexcel in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-03-2021, 04:54 AM
  2. [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
  3. [SOLVED] Look for a quicker code to replace this loop approach
    By billj in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-29-2017, 08:03 AM
  4. [SOLVED] Replace accented characters in worksheet name to regular characters
    By kingsdime29x in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-18-2017, 10:59 AM
  5. [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
  6. [SOLVED] Help for v-lookup approach instead of manually cell linking approach - Excel 2010
    By Mirisage in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-31-2013, 01:32 PM
  7. Inserting french characters using a form approach?
    By reddwarf in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-16-2009, 04:20 AM

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