Free Microsoft Excel 2013 Quick Reference

# Countif cell contains certain text

I am trying to find a formula that will count the number times the word "miss" appears in a range of cells that contain the text "miss" and a value. My cells say: Miss (3.4%) or Make (5.0%) and the like. I now need to count how many "misses" and "makes". Is their a COUNTIF that will search the cells and count the ones that have "MISS" and "MAKE"? Something like: =Countif(range, contains "MISS"). Thanks!

## Related Results

### Merge cells if cell contains certain text

Is it possible to use a macro that merges a cell if the cell contains certain text "AWAY", I want it to automaticaly select the cells within range \$B\$5:\$U\$42. So if \$B\$5 contains "AWAY" I want it to merge (b5,b6,c5,c6)

### Checking to see if a cell contains certain text

I want to check a cell to see if it contain certain text:

EX:
Type Cost
20 oz .08
Package10 0

How do i check to see if type contains the word "package"

Thanks for any help.

### Check if cell contains certain text

Hi All,

I'm looking for a function that can check to see if a cell contains specific text. For Example:

I have many cell that contain some, all, or none of the following 4-character text:
|C180|, |D410|, |S531|, |T844|
and I would like to know if, let's say D410, is contained in a certain cell. I will be checking for this in an array.

I also have another quick question. Is sumproduct or sum(if(.....)) quicker if they are both checking the same amount of criteria?

Jason

### Check if cell contains certain text

Hi All,

I'm looking for a function that can check to see if a cell contains
specific text. For Example:

I have many cell that contain some, all, or none of the following
4-character text:
|C180|, |D410|, |S531|, |T844|
and I would like to know if, let's say D410, is contained in a certain
cell. I will be checking for this in an array.

I also have another quick question. Is sumproduct or sum(if(.....))
quicker if they are both checking the same amount of criteria?

Jason

--
jhockstr

### Selecting rows when cell contains certain text

I want to include in a macro I already have that if a cell contains a certain text, it highlights the row, but not just that one row, all the rows with the text, any ideas?

### Use SUMPRODUCT to count cells which CONTAINS certain text/numbers

I am attempting to sum the number of occurrences for the following:
(1) Column C contains the number 3 (all cells in this column contain both text and numbers)
(2) Column B equals "Open"
(3) Column K equals "Design"

This is my working formula, but isn't working. The error is in the first array, I've forgotten how to find/sum cells which contain certain text/numbers.

=SUMPRODUCT(
--ISNUMBER(MATCH('AIM data'!\$C\$2:\$C\$10000,{"*3*"},0)),
--('AIM data'!\$B\$2:\$B\$10000="Open"),
--('AIM data'!\$K\$2:\$K\$10000="Design"))

TIA -James

### Formula needed: count if cell CONTAINS certain text

What formula do I need to count the cells that CONTAIN a certain text
(<> equal!)
e.g.
A1 = John
A2 = Pieter
A3 = William

count cells that CONTAIN "i": 2 (A2 and A3)

What (set of) formula should I use? Excel Help doesn't give me the

Wim

### Delete all cells containing certain text

I have long text lists that require certain values to be removed but it may not be the entire value of the cell. So As I'm searching for let's say ".ht" I can replace all values with "%%%%%%%%%%%%%%%%%%" but I want a macro that says any cell containing "%%%%" gets deleted (the whole cell not just the %'s). This should be possible, no? How would I perform such a task.

### IF fuction when another cell contains certain text

i need help with an IF statement to the effect that the logical function part needs to look in another cell to see if it contains the text GWS or Graham Wood.

### How to sort column when cells contain both text & number (Sample X

How to sort column when cells contain both text and number, that is
Sample 1
Sample 2
Sample 3
etc.

When I sort as usual I get the undesired order
Sample 1
Sample 10
Sample 100
Sample 101
etc.

Sample 1
Sample 2
Sample 3
etc.

This is problem is probably related to that Excel takes the cell content
only as text.
--
Hreinn

### How to find cells containing exact text, and separate them

Hi

If anyone knows how to find cells containing exact text, e.g. email address,
and separate/copy them to a separate list or column of cells.

Sheet has about 600 rows so doing it one by one with search function is not
appealing - there should be an automated way/formula to separate these cells.

All help appreciated !

### Determine if cell contains any text in VBA

Hi guys

I have tried searching for a way for VBA to recognise in VBA if a cell contains any text, but most of my searches respond to specific cases for specific characters in a string

Basically, I'm just looking for the correct way to word a string of code that if it detects any text in column B (col 2 ) then it should not do anything, else it should cut the value in col 2 and paste it in col 8

```'''''''''''''''''''''''''''''Check for values in column
B''''''''''''''''''''''''''
' For i = Range("A65536").End(xlUp).Row To 5 Step -1

If Cells(i, 2).Value   "<> TEXT? this is where I need help"  Then Cells(i, 2).Cut Destination:=Cells(i, 8)

Next```

Similarly, this can be rethought as determining if the cell is an integer, ie if it is an integer (and not text) then cut from column 2 and paste to column 8

Thank You

### How to insert blank row below a cell containing specific text

Good Afternoon Friends,

I was wondering if someone might be able to assist me with using VB to insert a new row below a cell containing specific text.

For example:

- All of my data is in column A
-I want to scan all of column A, and if there is a cell that contains "ACHCAMERIGROUP M", then I want a blank row inserted below it. If column A does NOT contain that text....do nothing.

Laura

### Format only cells containing certain word

Today I was trying to do something - I had a range which contained some
cells containing the text string 'Manufacturer:' (my inverted commas)
and wanted to format only those cells.

Now, I couldn't autofilter, advanced filter or sort because of the sheet
structure, and amending it would have taken as long as manually
formatting the relevant cells. So I was thinking of how to do it. I was
thinking of a conditional format of any cells containing the string. But
non-numerical (or non-formula) conditions don't appear to be possible.

Could anyone tell me if and how it is possible. Or, alternatively,
another way to format cells within a range containing a specified text
string? (Although they contain the word 'Manufacturer:' along with the
manufacturer name which changes from cell to cell.)

Any help would be greatly appreciated.

-Jay-

### If a cell contains a text from a cell?

If a cell contains a text from a range of cells (A2:A32), then it equals the cell next to the corresponding cell. Example, if I say C2 equals text from D3 (Chicago), then it searches in range A2:A32, and chicago happens to be cell A17, then it is equal to the cell next to A17, B17. But A17 and B17 will not always be chicago, it could be a different city. Is that at all possible?

### Insert rows beneath cells containing specific text

Hi,

I am trying to format a large number of tables for pasting into a Word doc.

Is there an easy way to insert a row on a worksheet beneath any cell containing specific text - in this case, 'market summary'.

Many thanks,

### What FORMULA will count records that CONTAIN certain text?

I have a survey that allows mulitple answers. What formula can I use to
count the records that contain certain text? I can Filter the responses for
those records that contain the data, but I want to use a formula instead.

### Cell containing both text and number. how to total number alone?

Respected sir,
at column [B] cells contains both text and a number. There are about 100 rows. Is it possible only to sum the number omiting the text? if sum and plus sign is added "#value" is flashed. if sum and comma is used, value "0" is returned.
thanking you,
yours faithfully
preventhospitalvisit

### If cells in a column contain chosen text then print other text in adjacent cell.

I have a spread sheet that is about 8000 rows. I need code to check if cells in Column D contain certain text (in this case: \$50) and if so print this text: ___ \$100 into the adjacent cell in Column E.

D E
\$40
\$30
\$10
\$50 ___\$100
\$10
\$50 ___\$100

### Run Macro If Cell Contains Text

I'm pretty familar with excel, and can use the macro recorder with no problem but don't know VBA. Here's my problem.

I have several Macros created, but i want to create a single macro that will look at a cell and according to certain conditions run the currect macro.

Specifically
If current active cell contains certains a certain word in the beginning of the text string, then run macro 1
If current active cell contains certains a certain word at the end of the text string, then run macro 2
If current active cell contains certains a certain word anywhere else in the text string, then run macro 3
And current active cell doesn't contain the certain word in the text string then run the macro 4

This may be too hard to do, i don't know. Somehow making an If statement within a macro to see a condition and then run a macro if it's true.

Thanks
~Chris

### If a cell does not contain a certain text => delete the entire row

- check is a cell contains the text "XXX" (not equal...)
- if the cell contains the above mentioned text => delete the entire row?? thanks in advance.

### Finding certain text placed randomly in a set of cells

I want to sum column B if the cells in column A contain certain text ("SWS"). SWS could be placed randomly in each cell. For instance:
COLUMN A COLUMN B
abcSWS123 10
SWSaaa 20
1SWSabcd 30
SWS 40

I know to use a sumif statement and name column A as a range, but how do I extract SWS from each cell?

Thanks, Jim

### Loop through the results of Find / Add Hyperlink to all cells containing certain text

Hi there,

I was wondering if there is anyway to add a certain hyperlink to all cells where the entire cell contents match a certain value.

I know how to do this by looping through all sheets and cells, but my workbook has over 60 sheets and there are over 70 different values of cell contents that should have hyperlinks. Therefore I'm wondering if there is any quicker way.

One way I thought of was to do a find and replace the value with the hyperlink formula, but I am not able to do this. The text must remain the same in the cells.

Is there any way of looping through all the results of a find and gettting the address of each one?

Thanks in advance for any suggestions on how to complete this tricky task!

js

### Formula when cell contains certain text

Here's a situation:

cell A1 is linked to a combobox, giving either of the following result:
2b2t
2b2t w fix return
swing w fix return

If A1 contains text with "fix" within, B1 is to bring back a result of 1; otherwise, have it mention 0.

I tried using the SEARCH and FIND functions, but if "fix" is not found, it returns VALUE.

Anything I'm doing wrong?

btw....here's the string i came up with....
=if(search("fix";A1)>0;1;0)