Can't find the ampersand character in a character string

[teachman]

Hello,

I'm using Excel 2016 and am trying to find the ampersand character (&) in a cells that are just characters. There is no = at the beginning of the string. Something like Victory & International.

I've googled for that and didn't find anything the pertains to my situation.

So, how do I search for the & character in a cell that is not a formula?

Thanks,

George Teachman

 

[Joe4]

What exactly are you trying to do? Identify where in the cell it exists?

If so, try (for an entry in cell A1):

=FIND("&",A1)

 

[teachman]

I'm really just trying to find cells with & in them. I'm not interested in where the & is in the cell.

Thanks,

George Teachman

 

[Joe4]

You could simply wrap it in an IF statement, something like:

=IF(ISNUMBER(FIND("&",A1)),"Found","None")

 

[Fluff]

Are you trying to find the cells using Ctrl F, a formula, or VBA?

 

[teachman]

With Ctrl F

 

[Rick Rothstein]

With Ctrl F

With the Find dialog box open, click the "Options>>" button and make sure there is no check mark in the "Match entire cell contents" item.

 

[teachman]

Well, son of b*.

Just to be clear, I didn't have that check box checked. I originally started this trip thinking that the & was one of those special characters and I tried multiple ways of telling Excel that - "&", "*&*", ~& all without the "Match entire cell counts" checked. All failed. It did not occur to me that the & was not a special character. After all, it has a special meaning in Excel.

But, using your suggestion and starting from scratch, I just put the & character, by itself, in the Find field and left everything else unchecked and ran the Find and it worked! Then, just to be sure, I checked the Match Case box and the Find still worked.

This forum is awesome, as always.

Thanks, Rick

George Teachman

 

[Rick Rothstein]

Just to be clear, I didn't have that check box checked.

I have always hated the Find and Replace dialog boxes for using a "Options>>" button and hiding so few options. Why? Because Excel remembers the Match Case and Entire Cell Contents options (plus some others) from the last time they were set, whether that setting was manual in the dialog box or via VBA code, so hiding an option that is remembered like that makes no sense. I could understand if there were a lot of options, but after pressing the "Options>>" button, the dialog box does not change size all that much and will fit on anyone's monitor no matter what there pixel count is, so I don't understand why Microsoft chose to hide some of the options like that.