# Count cells that contain anything OTHER THAN - Help Please

#### mollys

##### New Member
Hello all -
Hoping you can help me out here. I am trying to count up cells that contain freeform text and can't seem to figure it out.

The column is a 'How did you hear about us' list and it includes three standard options plus a freeform other field. Users can select multiple options so I end up with a column like this:

 Facebook, Instagram Instagram Facebook, A friend told me about you I walked by an event Instagram, My mom

<tbody>
</tbody>

I am looking to count up the cells that contain ANYTHING other than "Facebook" or "Instagram". In this example, the count would be 3.

Any help would be greatly appreciated. Thanks so so much.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Tetra201

##### MrExcel MVP
Could you please clarify the logic behind the expected result of 3?

#### JrayK2

##### New Member
There might be a better way to do it but this should work (replace A1 relevant cell reference)

#### mollys

##### New Member
Could you please clarify the logic behind the expected result of 3?

Three of the cells include text other than the standard answers of "Facebook" and "Instagram" -
"I walked by an event", "My mom", and "A friend told me about you" are the freeform answers that I would like to count. So I'm trying to count cells that contain any text BESIDES the standard answers. Does that make sense?

#### JrayK2

##### New Member

Three of the cells include text other than the standard answers of "Facebook" and "Instagram" -
"I walked by an event", "My mom", and "A friend told me about you" are the freeform answers that I would like to count. So I'm trying to count cells that contain any text BESIDES the standard answers. Does that make sense?
What is the 3rd standard option? Besides instagram and facebook

#### kweaver

##### Well-known Member
While this is ugly, it works (I think).
If your responses are in the A column (starting in row 2; header in row 1; I tested with 6 responses; adjust the formula accordingly), then

B1: =COUNTA(A:A)-1-COUNTBLANK(B2:B7)

This substitution removes the key words, the ,s and all spaces. Therefore, your first two entries would be blank but the others would have text.
Fill B2 down to however many rows you have.

If there might be other characters other than a comma and space, you could (a) add more substitution instructions or (b) would have to go to VBA.

Here's a UDF (User-Defined-Function) in VBA that could be placed in, say, F2 and fill down. Put the same B1 formula in F1
I put a name range, remove, in D1:Dn which could include a space, comma, the key words, etc., etc.
Then, in F2 write: =REMOVE_STUFF(A2,remove) and fill this down.
Now the UDF will remove all of the undesired characters like a group of SUBSTITUTES would.

HTH

Code:
``````Function REMOVE_STUFF(strInput As String, rngFind As Range) As String

Dim strTemp As String
Dim strFind As String

strTemp = strInput

For Each cell In rngFind
strFind = cell.Value
strTemp = Replace(strTemp, strFind, "")
Next cell

REMOVE_STUFF = strTemp

End Function``````

Last edited:

#### mollys

##### New Member
The third option is Google. It didn't show up in the cells that I copied in my example, but does show up in the field.

#### Eric W

##### MrExcel MVP

ABC
2Instagram
4I walked by an event
5Instagram, My mom

</tbody>
Sheet5

Worksheet Formulas
CellFormula

</tbody>

<tbody>
</tbody>

1,102,907
Messages
5,489,659
Members
407,703
Latest member
Chibuzo

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...