Excel search/replace does not replace

Question

I am running Excel for Mac 16.22 Office 365. Sometimes (not all the time) when I search/replace, Excel reports it has replaced the target text, but nothing changes. I then have to replace each and every occurrence manually. This does not happen all the time, just sometimes. This makes it all the harder to resolve.

Answer 1

Hi Ivy

There does not seem to be a pattern in how the problem occurs, ie whether specific characters or circumstances trigger it. It's usually a mixture of text, numbers and special characters searching,replacing within an highlighted column. Often, it's deleting the characters, sometimes replacing them with other characters, eg with a column containing merchandise sizes, "COMMEMORATIVE TSHIRT : S" will be replaced by "S" by searching for "COMMEMORATIVE TSHIRT : " and replacing it with nothing, leaving the S untouched.

The action returns the message "All done. We made 12 replacements.", but nothing changes in the highlighted column and I then have to go through the column changing each cell manually. Then I'll attempt another search/replace process, but it will work.

I have been doing this same process for years with no problems. This issue has arisen only with 16.22 and 16.24.

I could upload the entire file, which is 2.3mb, or I could take the sheet out of it and upload that. I have done this with similarly sized files in the past without issue.

Thanks

Paul

Answer 2

Hi oceanswims, 

Sorry for the inconvenience caused by this issue. 

To troubleshoot this issue, you could firstly follow Step 2 mentioned in this article (Remove files from your user Library folder) to remove com.microsoft.Excel folder. Restart your Mac and Excel to check the outcome. 

If this issue persists, to check if it is account related, please creating a new Mac account. Perform Find & replace after signing into Mac with the new account. See if the issue is related to a corrupted user profile. Please have a try and share with me your outcome.

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.​ 

Regards,

Neo

Answer 3

Hi Ivy

Please see my response just now to Sukie's post.

Paul

Answer 4

Hi oceanswims,

Please let us know your results after trying this solutions and we’ll be glad to provide further assistance.

Regards,

Ivy

Answer 5

Hi oceanswims,

Glad to help you to resolve this problem. Based on Update history for Office for Mac, our Office for Mac the latest version is 16.24 (19041401). the latest version always having the most new features, all bug fixes and security updates. So I strongly suggest you to update your Office. Could you follow this steps to update the Office:

• If you download Office from office.portal:

1. Open any Office application such as Word, Excel, PowerPoint, or Outlook.
2. On the top menu, go to Help > Check for Updates.
3. Under "How would you like updates to be installed?", select Automatically Download and Install.
4. Select Check for Updates.

(Before update, make sure you have latest version of Microsoft AutoUpdate tool)

• If you downloaded Office from the Mac App Store:

1. Open the Mac App Store from your Dock or Finder.
2. Click Updates on the left side menu.
3. Click Update All, or click the Update button next to the apps that you want to update.

In addition, to replace all the workbook make sure you do this before you replace:

• Search a sheet or a whole workbook after you click replace.

• Don’t search by rows or by columns except you choose the whole cells you want to search.

• In the search field, to search only for specific types of information, click Advanced Search, and then on the Look in pop-up menu, click Formulas, Values, or Comments.

• In the search field, to search for case-sensitive content, click Advanced Search, and then select the Match case check box.

• In the search field, to search for exact matches only, click Advanced Search, and then select the Find entire cells only check box.

For your reference, this articles may help. Find and replace text in a Numbers spreadsheet.Find and replace text or numbers in Excel for Mac

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

Please let us know your results after trying this solutions and we’ll be glad to provide further assistance.

Regards,

Ivy