IF Formula Using Wildcard to Find Part of Text

stevelockridge

New Member
Joined
Nov 13, 2013
Messages
6
I am trying to write a formula that will look at the contents of a cell (D19) and see if it contains the text "R-". If D19 contains "R-", then value of D21 for which I am writing the formula would be "240V 1-phase". If D19 doesn't contain "R-", then the value in D21 would be "460V 1-phase".

The values of D19 are like this: R-1, R-2, R-3, R-5, etc.
It can also have these values: RH-1, RH-2, RH-3, RH-5, etc.

What is the wildcard that would tell the formula to look for "R-"?

I have a working formula but there is no wildcard in it. =IF(D19="R-1","240V 1-phase","460V 1-phase")

I have tried using "R-*" but that is not working. Thanks.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
I am trying to write a formula that will look at the contents of a cell (D19) and see if it contains the text "R-". If D19 contains "R-", then value of D21 for which I am writing the formula would be "240V 1-phase". If D19 doesn't contain "R-", then the value in D21 would be "460V 1-phase".

The values of D19 are like this: R-1, R-2, R-3, R-5, etc.
It can also have these values: RH-1, RH-2, RH-3, RH-5, etc.

What is the wildcard that would tell the formula to look for "R-"?

I have a working formula but there is no wildcard in it. =IF(D19="R-1","240V 1-phase","460V 1-phase")

I have tried using "R-*" but that is not working. Thanks.
You could do it this way...

=IF(COUNTIF(D19,"*R-*"),"240V 1-phase","460V 1-phase")
 
Upvote 0
You could do it this way...

=IF(COUNTIF(D19,"*R-*"),"240V 1-phase","460V 1-phase")


I'm sorry but I have to make it a bit more complicated. There are essentially three values of D19 that will control what appears in cell D21. They are:

1) *R-* should return "240V 1-phase"
2) *RH* should return "460V 1-phase"
3) Blank should return an empty D21 cell

I have tried several different things but I am not a Excel expert. I know it is probably something simple I am missing. Thank you.
 
Upvote 0
I'm sorry but I have to make it a bit more complicated. There are essentially three values of D19 that will control what appears in cell D21. They are:

1) *R-* should return "240V 1-phase"
2) *RH* should return "460V 1-phase"
3) Blank should return an empty D21 cell
Maybe this...

=IF(D19="","",IF(COUNTIF(D19,"*R-*"),"240V 1-phase",IF(COUNTIF(D19,"*RH*"),"460V 1-phase","")))
 
Upvote 0
Maybe this...

=IF(D19="","",IF(COUNTIF(D19,"*R-*"),"240V 1-phase",IF(COUNTIF(D19,"*RH*"),"460V 1-phase","")))

This is strange. I copied and pasted the formula and when I pressed 'Enter', the formula showed up in the cell (no errors), and I can only see the top half of the formula text, like it is wrapping in the cell and getting pushed down.

excel_formula.jpg
 
Upvote 0
Maybe you grabbed the Line Feed character in front of the equal sign? It may also have something to do with your font size setting (looks like it may be large) and/or possibly the vertical alignment (looks like it might be centered to me) for the cell you put it in. However, none of that should really matter as it's a formula and won't show up in the cell after its committed.
 
Upvote 0
Now it is doing something different. The formula is no longer showing up in D21. Now the cell says "TRUE", no matter what the value of D19 is. Even if it is blank.
 
Upvote 0
Well, good news and bad news. I did check for extra characters. That's when it started saying "TRUE". I finally deleted the formula and re-pasted it. Guess what? Now it works. Thank you so much for your help.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

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