Can't find the ampersand character in a character string

teachman

Active Member
Joined
Aug 31, 2011
Messages
303
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,140
Office Version
365
Platform
Windows
What exactly are you trying to do? Identify where in the cell it exists?

If so, try (for an entry in cell A1):
Code:
=FIND("&",A1)
 

teachman

Active Member
Joined
Aug 31, 2011
Messages
303
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,140
Office Version
365
Platform
Windows
You could simply wrap it in an IF statement, something like:
Code:
=IF(ISNUMBER(FIND("&",A1)),"Found","None")
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
Are you trying to find the cells using Ctrl F, a formula, or VBA?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,421
Office Version
2010
Platform
Windows
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

Active Member
Joined
Aug 31, 2011
Messages
303
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,421
Office Version
2010
Platform
Windows
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.
 

Forum statistics

Threads
1,078,145
Messages
5,338,507
Members
399,238
Latest member
amuthan10

Some videos you may like

This Week's Hot Topics

Top