+ Reply to Thread
Results 1 to 6 of 6

Find and Replace value deletes entire cell contents

  1. #1
    Registered User
    Join Date
    08-17-2012
    Location
    Chicago, IL
    MS-Off Ver
    Office 365
    Posts
    35

    Find and Replace value deletes entire cell contents

    I am not new to Excel and cannot understand why I cannot find the answer to this problem.

    I have a colleague who is trying to do a Find and Replace (actually a Find & Remove) and I cannot recreate the issue on my end to tell him why it's not happening the way he wants.

    Sample of the cell values:
    MAS0110 Total
    MAS0111 Total
    MAS0118 Total
    MAS0119 Total
    MAS0123 Total
    MAS0125 Total

    He wants to find " Total" and Replace with nothing. I trust his level of expertise and he has done this numerous times before and he gets the wanted results.

    MAS0110
    MAS0111
    MAS0118
    MAS0119
    MAS0123
    MAS0125

    What is happening is that his search string is deleting the entire contents of his cells. He sent me the file, when I try it, mine works okay. We tried finding just "Total" (no leading space) and that works fine but leaves the trailing space. We could always trim the cell but at this point we want to know why this is happening now when it has not for him in the past.

    I have tried taking the values of his cells into a text editor and it looks like a regular space character preceding "Total".

    Now here's the weird thing. If I have him put just a "space" in the Find box, and then hit replace, it grabs the word "Total" along with it and gives him the desired results.

    At this point I'm thinking it may be one of his settings but can't imagine where. Thanks for the help!

  2. #2
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: Find and Replace value deletes entire cell contents

    Question patb:

    Is the word: " Total" the result of a form, style, subtotal field, or pivot table, or is it just text within the cell with the number?
    Pete

  3. #3
    Registered User
    Join Date
    08-17-2012
    Location
    Chicago, IL
    MS-Off Ver
    Office 365
    Posts
    35

    Re: Find and Replace value deletes entire cell contents

    The values were sourced from a different worksheet in the same workbook. He was using the OutlineSubtotals option and had it collapsed to Level2.

    Then he highlighted the resulting rows, selected "visible cells" only, got the marching ants, did a ctrl C for copy, went to the blank worksheet and did a "paste special, values". I can vouch for this as I watched him as he did it. Then he went to do the Find and Replace and that's when it went awry. He sent me the same file, I went through the same steps and it works fine for me.

    I must have had him try it a dozen times and it always gave him the same results - deleting the entire cell contents. Hope you have an idea of why this is happening.

  4. #4
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: Find and Replace value deletes entire cell contents

    Is there a *wildcard character in the F&R dialogue box?
    Is his version of Excel different than yours?
    After pasting to the blank worksheet, you can convert the entire cell contents to text with a helper column = TEXT(A2,0), then copy that, paste special; values, then do the F&R.


    Go Cubs!
    Pete

  5. #5
    Registered User
    Join Date
    08-17-2012
    Location
    Chicago, IL
    MS-Off Ver
    Office 365
    Posts
    35

    Re: Find and Replace value deletes entire cell contents

    I think I found the problem. PeteABC123 pointed me in the right direction. Instead of just looking at the OutlineSubtotal sheet, I went back further to see how that was created.

    It was a combination of data from 2 worksheets:
    Worksheet 1 had 2 columns "Part No" & "Qty"
    Worksheet 2 had 3 columns "Part No" & blank column & "Qty"

    When the user created Worksheet 3 he copied data from both Wkshts 1 & 2 directly under each other. Then he noticed the columns were not in alignment. It was as this point that he highlighted the blank cells in COL B and deleted them shifting COL C over for those rows. While it looked okay and subtotaled okay, something must have messed that up for the Find & Replace.

    We recreated Worksheet 3 by first deleting the blank column B in Worksheet 2 and then copying the data to Worksheet 3. Now his Find & Replace for " Total" is working fine!

    Didn't want to leave anyone hanging if they ever have the same problem!

  6. #6
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: Find and Replace value deletes entire cell contents

    Excel can get messy.
    Glad it worked out. Feel free to mark thread as SOLVED (thread tools; near the top) if it's been solved.
    Cheerio.

    Pete

+ 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. Find and Replace the contents of one cell with the contents of another
    By thechevs in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-30-2014, 06:48 AM
  2. Find and replace entire content in cell
    By dkay1973 in forum Excel General
    Replies: 1
    Last Post: 07-25-2013, 08:18 PM
  3. Find & Replace Entire Cell Contents
    By dwhite30518 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-01-2013, 12:39 AM
  4. Double clicking protected cell deletes entire contents
    By oenleunc in forum Excel General
    Replies: 11
    Last Post: 09-30-2012, 02:45 PM
  5. Replies: 0
    Last Post: 09-23-2010, 07:01 PM
  6. Replies: 0
    Last Post: 09-23-2010, 06:39 PM
  7. Find 1 character and replace entire cell
    By s2nguyen in forum Excel General
    Replies: 2
    Last Post: 11-07-2008, 12:44 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