surkdidat

Active Member
Joined
Oct 1, 2011
Messages
361
How do I amend the code in

IF(ISNUMBER(SEARCH"XXX" ...etc

so if it ISNT "XXXX" please
 

Some videos you may like

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.

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
361
Apologies, I rushed the post as I had to go off to a meeting!!

I have

Code:
IF(ISNUMBER(SEARCH("XXXX",K5)),K5,........

However, I want it to look at the value in K5, and if it does not contain XXXX then I want it to display that value, if it contains XXXX, I need it to move onto the next part of my logic
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117

ADVERTISEMENT

Like:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">dssdd</td><td style=";">dssdd</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">dssddXXXXjklz</td><td style=";">next step</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L5</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">SEARCH(<font color="Green">"XXXX",K5</font>)</font>),K5,"next step"</font>)</td></tr></tbody></table></td></tr></table><br />
 

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
361
Hi there

It needs to CONTAIN "XXXX" so this can be anywhere in the text in the relevant field....[

QUOTE=sheetspread;5029051]Like:

Excel 2010
KL
5dssdddssdd
6dssddXXXXjklznext step

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet5

Worksheet Formulas
CellFormula
L5=IF(ISERROR(SEARCH("XXXX",K5)),K5,"next step")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

[/QUOTE]
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
If the target in K5 must contain "XXXX", then:

=IF(ISNUMBER(LOOKUP((9.99999999999999E+307,SEARCH("XXXX",K5)),"next step",K5)

If the target in K5 must NOT contain "XXXX", then:

=IF(ISNA(LOOKUP((9.99999999999999E+307,SEARCH("XXXX",K5)),"next step",K5)

Note that these will not distinguish between XXXX and XXXXX.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,618
Messages
5,523,935
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top