+ Reply to Thread
Results 1 to 12 of 12

excel 2013 spreadsheet will not search and replace '= with =

  1. #1
    Registered User
    Join Date
    01-22-2017
    Location
    London, Ontario
    MS-Off Ver
    2013
    Posts
    2

    excel 2013 spreadsheet will not search and replace '= with =

    I have an excel 2013 spread sheet with column of referenced cells and I did a search and replace of = to '= to change it to a text cells. I then did a copy and transpose paste to a row.. I then need to change the cells back to formula. I have tried the Find and Replace to change '= back to just = but excel says it cannot find it? This works on other sheets but not this one eg I want to change '='REFERENCE ONLY'!K109 to ='REFERENCE ONLY'!K109 Any suggestions??

    Thanks

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: excel 2013 spreadsheet will not search and replace '= with =

    Try

    Find: ?=
    Replace =

    Cell will still be formatted as TEXT so you need to change to GENERAL

  3. #3
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,858

    Re: excel 2013 spreadsheet will not search and replace '= with =

    The reason is that Find and Replace does not recognize a single quote.
    You can replace = with '= but strangely you cannot replace '= with = because Excel can't find the ' at the beginning of text.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  4. #4
    Registered User
    Join Date
    01-22-2017
    Location
    London, Ontario
    MS-Off Ver
    2013
    Posts
    2

    Re: excel 2013 spreadsheet will not search and replace '= with =

    I tried the Fine: ?= and replace = Got the same message: Microsoft Excel cannot find a match

  5. #5
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,858

    Re: excel 2013 spreadsheet will not search and replace '= with =

    Try as
    Select the column and then COPY it.
    Choose an empty column and use Paste Special and choose the Values option.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: excel 2013 spreadsheet will not search and replace '= with =

    This code seemed to work, depending on whether there was single quote or not in the cell but it would be better if you posted a sample file:

    Please Login or Register  to view this content.
    OR

    cell formatted as TEXT containing ='REFERENCE ONLY'!K109 (no single quote in text string)

    Please Login or Register  to view this content.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,779

    Re: excel 2013 spreadsheet will not search and replace '= with =

    It would be better to replace the = with something like $$= to begin with, which Excel will then interpret as a string, and then you can later change $$= back to = with Find & replace. I've chosen $$ as it is unlikely to appear elsewhere in the formula.

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    10-28-2008
    Location
    Turkey
    Posts
    86

    Re: excel 2013 spreadsheet will not search and replace '= with =

    .

    The single apostrophe is a hidden character and Find/Replace will not see it. So you can use formulas like these:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Or Text to Column

    .

  9. #9
    Registered User
    Join Date
    10-28-2008
    Location
    Turkey
    Posts
    86

    Re: excel 2013 spreadsheet will not search and replace '= with =

    .................
    Last edited by yurttas; 01-22-2017 at 03:02 PM. Reason: Repated

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: excel 2013 spreadsheet will not search and replace '= with =

    Quote Originally Posted by Pete_UK View Post
    It would be better to replace the = with something like $$= to begin with, which Excel will then interpret as a string, and then you can later change $$= back to = with Find & replace. I've chosen $$ as it is unlikely to appear elsewhere in the formula.
    Even that didn't work for me.

    A1 contains this TEXT string =B1. If you look in the formula bar it displays as '=B1.

    Find/Replace
    Find what: =
    Replace with: ^^=
    Replace All

    A1 now contains the TEXT string ^^=B1. If you look in the formula bar it displays as '^^=B1. So, the ' is still there.

    Find/Replace
    Find what: ^^
    Replace with: =
    Replace All

    A1 now contains the TEXT string =B1. If you look in the formula bar it displays as '=B1. Back where we started.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,779

    Re: excel 2013 spreadsheet will not search and replace '= with =

    Hi Biff,

    I assumed from the first post that there was a formula in the cell and the OP changed the = to '=, which is what is causing the problems.

    I suggested that instead of doing that the OP should replace the = with $$= in the first place, and then change it back after manipulating the cells.

    It WONT work if you start off with '=, but that isn't what the OP's starting point was.

    Pete

  12. #12
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: excel 2013 spreadsheet will not search and replace '= with =

    Find: =
    Replace: [space]=
    Formulas will be treated as text

    back to previous

    Find: [space]=
    Replace: =
    (text) formulas will be treated as formulas

    or select range what you want (not select all )
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    or

    1. In an empty cell enter 1, then Copy that cell.
    2. Select the range with apostrophes before =.
    3. From the menu, Home-->Paste Special-->Multiply.
    4. Again from the menu Home-->Find & Select-->Replace-->In Find What, enter =, and in Replace With also = (silly, isn't it?). Hit Replace All.
    5. OPTION: Find: 0 Replace: (nothing)
    Last edited by sandy666; 01-22-2017 at 06:42 PM.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

+ 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] Cannot Find or Replace caret characters (Excel 2013)
    By nlexcel in forum Excel General
    Replies: 7
    Last Post: 05-13-2016, 10:19 AM
  2. VBA to find and replace from a table in excel 2013
    By Nirmall in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-17-2016, 01:44 PM
  3. Excel 2013 delete and replace a macro
    By Arismac in forum Excel General
    Replies: 2
    Last Post: 02-17-2016, 06:30 AM
  4. Excel 2013, how to us the “search” function like vlookup.
    By James Greenleaf in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-29-2014, 06:01 PM
  5. [SOLVED] Excel 2013 - VBA Find and Replace
    By colbyski90 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-12-2014, 09:51 AM
  6. search for value in one spreadsheet from a table in another spreadsheet and replace
    By mlhend2002 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2012, 01:48 PM
  7. How can I perform multiple search & replace - all data in spreadsheet?
    By Bucyruss in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-14-2006, 01:40 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