Using If statements with text

jpm021990

New Member
Joined
Sep 11, 2014
Messages
11
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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?
 
Upvote 0
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
 
Upvote 0
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","")))
 
Upvote 0
So would your original formula.

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?
 
Upvote 0
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
 
Upvote 0
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


 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top