4

When I try to use Find and Replace in Excel 2013 by entering a colon into the 'Find what' field and any text into the 'Replace with' field I get "That function isn't valid" error on clicking 'Replace All'.

enter image description here

That function isn't valid

I am guessing that the colon is some kind of special character or something. Is there an escape character or a certain type of quotation marks or square brackets I should be using?

Update I think it might relate to the fact the field starts with an @ symbol. But I want that there, I just don't want the colon at the end

Update 2

Thanks @Jerry I did try using a tilde and it does find the cells as before but also as before the same error message shows. Interestingly if I delete the colon myself in the cell then hit Enter, the same error message shows

Paul C
  • 619
  • Perhaps. I don't have Excel-2013 but it works fine on 2010. Try using find: ~:. The ~ is the escape character in excel. – Jerry Feb 18 '14 at 11:56
  • @Jerry I think it is all down to have an @ at the beginning of the cell – Paul C Feb 18 '14 at 12:02
  • Possibly, you could maybe try to convert the column first into text so that no function can be evaluated? – Jerry Feb 18 '14 at 12:18
  • @CodeBlend: Normally a single quotation mark ' is the escape character. – James P Feb 18 '14 at 12:22
  • @James not for the likes of Find and Replace, in that case it is a tilde ~ as @Jerry said – Paul C Feb 18 '14 at 12:23

2 Answers2

4

The cells need to have a single quote at the beginning in order to tell Excel to not interpret them literally.

Try this:

  1. Highlight column B by clicking the column header
  2. Open the Search and Replace dialog.
  3. In the Find what textbox, enter @
  4. In the Replace with textbox, enter '@ (an at sign preceded by a single quote)
  5. Click Replace All
  6. Do the search and replace you were originally attempting.
James P
  • 11,333
1

It does seem to have something to do with the @ as first symbol in a cell. I hd the same issue and tried the find and replace without selecting the few cells containing and starting with the @ sign. It worked.

Vincent
  • 11