+ Reply to Thread
Results 1 to 4 of 4

how to use wildcards in search/replace for existing formulas?

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    656

    how to use wildcards in search/replace for existing formulas?

    For some reason I'm having trouble using wildcards in the find/replace function.

    In the attached workbook, I want to nest the existing formulas beginning in cell U10 down and to the right within an
    Please Login or Register  to view this content.
    fix so that cells with errors will be blank.

    All of the formulas have the same construction but which contain different cell references. (the actual workbook has around 50,000 cells with this formula construction).

    According to the official MS documentation, Find/Replace is supposed to accept the asterisk wildcard for strings with multiple characters, but I haven't had success making this work.

    I'm sure the solution is obvious, but... for now, not for me!

    Thanks

    PS it won't matter to me if the find/replace attempts to wrap other formulas within the worksheet within the IFERROR function. I can fix these rather easily.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    434

    Re: how to use wildcards in search/replace for existing formulas?

    Hi - So you just want to edit your formulas so if the result is false you want a blank cell? If so, take a look at U10 in my file.

    Am not understanding why you need find/replace.
    Attached Files Attached Files

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,090

    Re: how to use wildcards in search/replace for existing formulas?

    Maybe try at U9

    =IF(AND(ROWS(U$9:U9)>U$2,ABS(INDEX($S$9:$S$26,MAX(1,ROWS(U$9:U9)-U$2)))>$U$1),IFERROR($T9/INDEX($T$9:$T$26,ROWS(U$9:U9)-U$2)-1,""),"")
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    656

    Re: how to use wildcards in search/replace for existing formulas?

    Sorry for being so late to respond. Bo your formula works, but my workbook is pretty large (over 2 million cells) and I'm trying to avoid functions like vlookup, index, etc. They slow it down (which ordinarily wouldn't be an issue, but I also use Solver to go through lots of iterations in cell $U$1, so I was hoping to just find a simple method of altering my own formula using search/replace. Microsoft's method, here: https://support.microsoft.com/en-us/...f-fdb42f892e90


    I couldn't get that to work, so I wound up just manually nesting my existing formulas in the iferror function, then copying down.


    But thanks for your help. I'll keep your solution in mind for future work.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help replace wildcards!!!
    By nguyenanhdung_vbd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-03-2020, 08:41 AM
  2. [SOLVED] Search and replace data between wildcards
    By lagiosman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-16-2015, 01:11 PM
  3. [SOLVED] Code to search and replace formulas
    By jshaw82 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-11-2014, 02:29 AM
  4. [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
  5. Search and Replace Part of Formulas
    By alainsi in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-30-2009, 05:48 AM
  6. wildcards in replace
    By ldashburnwest@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-01-2005, 08:25 PM
  7. [SOLVED] Search - replace NOT in formulas
    By Michael Preminger in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-18-2005, 05:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1