# Using If statements with text

#### jpm021990

##### New Member
Hi,

Im trying to check column A for specific text and if it finds it put a value in column 2, i have multiple if statements and they work, the problem is if i apply the function to the whole row, it pasts "false" in column 2 and it ends up freezing excel because it does it thousands of times

is there any way to stop it?

this is my function

=IF(ISNUMBER(SEARCH("BF-O",A:A)),"3",IF(ISNUMBER(SEARCH("BF-W",A:A)),"1.9",IF(ISNUMBER(SEARCH("BF-R",A:A)),"2.4")))

thanks

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Why not use COUNTIF?

Couple of questions.

Is BF-O the entire contents of the cell or will there be other text with it?
What do you mean the entire row, your formula only references a column.
Will there only be 1 of either of those 3 values in the column or can there be any combination of them?

Couple of questions.

Is BF-O the entire contents of the cell or will there be other text with it?
What do you mean the entire row, your formula only references a column.
Will there only be 1 of either of those 3 values in the column or can there be any combination of them?

im sorry i meant column, and it will either be BF-O, BF-R, BF-W or nothing in column A, my problem is that for the empty spots, its putting "false" in column B and its freezing the program because its putting so many "false"s

there will only be 1 value in each cell

Why not use COUNTIF?

COUNTIF wont work for me because if I have BF-W , i want 1.9 in the next column

Why won't it work?

=IF(COUNTIF(A:A,"BF-O")>0,"3",IF(COUNTIF(A:A,"BF-W")>0,"1.9",IF(COUNTIF(A:A,"BF-R")>0,"2.4","")))

Why won't it work?

=IF(COUNTIF(A:A,"BF-O")>0,"3",IF(COUNTIF(A:A,"BF-W")>0,"1.9",IF(COUNTIF(A:A,"BF-R")>0,"2.4","")))

I just tested it, its putting 3 for all of my 3 variables

If 'BF-O' was found anywhere in column A that formula would return 3 and the rest of the formula would be ignored.

Where are you actually putting the formula?

Are you trying to check the entire column?

Or are you trying to check what's in column A on the same row as that you are putting the formula in?

Ok, In column A, I either have "BF-W" , "BF-O" , "BF-R" , or nothing

In column B, im pasting the formula

I want the formula to give me a 1.9 next to BF-W , a 3.0 next to BF-O or a 2.4 next to BF-R and nothing if theres nothing in column A

the problem im having with

=IF(COUNTIF(A:A,"BF-O")>0,"3",IF(COUNTIF(A:A,"BF-W")>0,"1.9",IF(COUNTIF(A:A,"BF-R")>0,"2.4","")))

is that it gives me 3 for everything, even if theres nothing on that row

Replies
4
Views
219
Replies
1
Views
104
Replies
4
Views
134
Replies
18
Views
6K
Replies
4
Views
524

1,219,770
Messages
6,150,167
Members
450,937
Latest member
kattyg261

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back