+ Reply to Thread
Results 1 to 4 of 4

Find: Using wildcard, but don't replace with wildcard

  1. #1
    Forum Contributor
    Join Date
    11-17-2004
    MS-Off Ver
    Office 2016
    Posts
    527

    Find: Using wildcard, but don't replace with wildcard

    I am trying to find a way to replace one portion of a series of formulas. In order to copy the formula throughout the worksheet and to allow cell references to change accordingly, I used the cell dragging technique.

    Now however, I want to wrap the quote (") character around these cell references but I'm having a hard time with it.

    In the Search field of the search and replace dialog, I seem to be able to use a wildcard to locate the correct cell reference I'm after, but if I use a wildcard in the Replace field, it replaces the cell number with the wildcard.

    For instance, using K* in the Search field finds all cell references from K5 through K100 but the replace function (using "K*" as my replace data) makes every reference equal to "K*". I'd like to keep the cell number so I end up with "K5", "K6",..."K99", "K100", etc.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Find: Using wildcard, but don't replace with wildcard

    Can you post the formula (or a sample)?

    Why do you want to replace K5 with "K5" - surely the formula won't work then?
    Audere est facere

  3. #3
    Forum Contributor
    Join Date
    11-17-2004
    MS-Off Ver
    Office 2016
    Posts
    527

    Re: Find: Using wildcard, but don't replace with wildcard

    The formula I'm using is required to reference a cell name based on text in a cell ($M$2). Copying the formula down without the quotes around the cell reference (K7 in this example) causes Excel to consider it text and not a cell reference and therefore it doesn't change. I need it to change as I copy it down.

    =INDIRECT("'" & $M$2 & "'!" & K7)

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Find: Using wildcard, but don't replace with wildcard

    Try this version

    =INDIRECT("'" & $M$2 & "'!" &CELL("address",K7))

    using CELL function allows you to copy the formula down so that K7 changes as required.......but still gives you "K7" so that INDIRECT treats it as required

+ 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. [SOLVED] Find and replace words with a wildcard
    By boomboomblock in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-04-2013, 09:58 AM
  2. [SOLVED] VBA needed to find and replace text using a wildcard
    By jgupte in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-30-2012, 08:10 PM
  3. Wildcard Find and Replace
    By DaveSmith2020 in forum Excel General
    Replies: 5
    Last Post: 02-16-2009, 12:27 PM
  4. Replies: 8
    Last Post: 08-17-2005, 09:05 PM
  5. Preserving wildcard in Find and Replace
    By rdobson in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 05-12-2005, 09:14 PM

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