search a string of numbers for a number

klatlap

Well-known Member
Joined
Sep 1, 2004
Messages
597
I have a string similar to this 1,9,7,11,8,10

I need a formula that would find 11 , the string can be different lengths so the number is not always in the same position.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

So what do you want happened (result) once the formula finds it in the Text string, and what if it isn't there ?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
But what do you want to result? a label that says "Yes it exists"

=IF(IFERROR(SEARCH("11",A1),0)=0,"Does not exist", "Yes it exists")
 
Last edited:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146

ADVERTISEMENT

Use B1 formula if you want the value to look for (i.e. 11) hard-coded in the formula.
Use C1 formula if you might want to look for different values using a cell reference.

<b></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 /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">1,9,7,11,8,10</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style=";">Find What</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">1,9,111,7,11,8,10</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">1,9,111,7,112,8,10</td><td style=";">No</td><td style=";">No</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">11,9,7,110,8,10</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">1,9,7,211,8,10,11</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;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)">Sheet586</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)">B1</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">FIND(<font color="Green">",11,",","&A1&","</font>)</font>),"Yes","No"</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">FIND(<font color="Green">","&F$1&",",","&A1&","</font>)</font>),"Yes","No"</font>)</td></tr></tbody></table></td></tr></table><br />

Formulas will Not error for 1, 111, 211, 112, etc., formula will Only result "Yes" if 11 exist within string.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

so the above formula can see the difference between 11 and 1?



Try this:

=IF(IFERROR(SEARCH(",11,",A1),0)=0,"No", "Yes")
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
@ DanteAmor, That won't work if the 11 is in the beginning or end of the string, you must Also include commas before and after the cell containing the string.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
@ DanteAmor, That won't work if the 11 is in the beginning or end of the string, you must Also include commas before and after the cell containing the string.

@jtakw, You're right

Use this:

=SI(SI.ERROR(HALLAR(",11,", ","&A1&","),0)=0,"No", "Yes")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,159
Messages
5,527,145
Members
409,749
Latest member
esmarques

This Week's Hot Topics

Top