If Contains x or y display value.

[AndyGray]

=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

 

[FormR]

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]

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" .

 

[FormR]

So is number won't work because they also contain text. So would need more so "IF it contains 01 or 07" .

Did you try the suggestion?

 

[AndyGray]

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]

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.

Excel 2013/2016
	K	L
20	mob = 071234567	mob = 071234567
21	tel = 011234567	tel = 011234567
22	071234567	071234567
23	01234567	01234567
24	110711
25	990199
26	mob = 990199
27	mob =971234567
28	mob = +4471234567	mob = +4471234567
29	+4471234567	+4471234567
30	text 07123213	text 07123213
Sheet1

Cell Formulas
Range	Formula
L20	=IF(ISNUMBER(LOOKUP(1,-SEARCH({" 07"," 01"," +447"}," "&K20))),K20,"")