If Contains x or y display value.

AndyGray

New Member
Joined
May 31, 2017
Messages
30
=IFERROR(IF(SEARCH("*07*",K20),K20,0),"")

This will extract 07 from my cell and display any contacts with this number... However I want it to say....


If Cell K20 contains 07 or 01 then place it in current cell. If not display nothing...

(I'm trying to filter emails and phone numbers out)...

And I might want to advance this in the future to filter landlines and mobiles...

So i would need an additional formula saying "If the left 2 characters contain 01 then display this full cell in current cell" (this is for landlines) then for mobiles I will do 08.

Thanks
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows
If Cell K20 contains 07 or 01 then place it in current cell. If not display nothing...

Hi, you can try like this:

=IF(ISNUMBER(LOOKUP(1,-SEARCH({"07","01"},K20))),K20,"")

Or if you really wanted to know if it starts with 07 or 01, rather than just contains.

=IF(OR(LEFT(K20,2)={"07","01"}),K20,"")
 

AndyGray

New Member
Joined
May 31, 2017
Messages
30
Hi, you can try like this:

=IF(ISNUMBER(LOOKUP(1,-SEARCH({"07","01"},K20))),K20,"")

Or if you really wanted to know if it starts with 07 or 01, rather than just contains.

=IF(OR(LEFT(K20,2)={"07","01"}),K20,"")

Some of the fields have had data added to them such as "Mob = 07" As i've ran that i've just discovered this problem. So is number won't work because they also contain text. So would need more so "IF it contains 01 or 07" .
 

AndyGray

New Member
Joined
May 31, 2017
Messages
30
I'd tried it and it was bringing back no values. In the end i've collated the data into 1 cell for now into one field and will be addressing this in the future. Because our data contains text at the start of some numbers, some 07 will be +447 and some 01 numbers will have 07 somewhere else in the number there was a lot of legwork to do for the result it would achieve.

Thanks.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows
I'd tried it and it was bringing back no values.

Hi, in situations like this it's always best if you can post some actual examples of your inputs so we have something to test with.

our data contains text at the start of some numbers, some 07 will be +447 and some 01 numbers will have 07 somewhere else in the number

in lieu of some sample data, here are some minor modifications to handle those variations.

<b>Excel 2013/2016</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;">20</td><td style=";">mob = 071234567</td><td style="background-color: #FFFF00;;">mob = 071234567</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">tel = 011234567</td><td style=";">tel = 011234567</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">071234567</td><td style="text-align: right;;">071234567</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;">01234567</td><td style="text-align: right;;">01234567</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;;">110711</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: right;;">990199</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style=";">mob = 990199</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style=";">mob =971234567</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style=";">mob = +4471234567</td><td style=";">mob = +4471234567</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style=";">+4471234567</td><td style=";">+4471234567</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style=";">text 07123213</td><td style=";">text 07123213</td></tr></tbody></table><p style="width:3.6em;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)">Sheet1</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)">L20</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">LOOKUP(<font color="Green">1,-SEARCH(<font color="Purple">{" 07"," 01"," +447"}," "&K20</font>)</font>)</font>),K20,""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,311
Members
414,052
Latest member
Dual Showman

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
Top