Search code examples
excelif-statementexcel-formulaexcel-2007vlookup

A formula to Find/Replace mobile telephone numbers in text format

I have a column where I input mobile telephone numbers. Here's sample input for Column A (text format): 

+639154112315  
+639171214125  
+639179120519

I have a column (B) where I input mobile numbers (text format) that are for removal e.g.:

+639171214125

So Column C should display:

+639154112315
+639179120519

In Cell C I want to display the mobile numbers from Cell A but not any numbers from Cell B. I tried VLOOKUP and it doesn't work (for Cell C): 

=IF(ISNA(VLOOKUP(B2,A:A,1,FALSE)),"",A2)

Is this possible in Excel?

Solution

  • Taking a guess that by Cell you mean Column, the following (copied down as appropriate) might suit;

    =IF(IFERROR(MATCH(A1,B:B,0)=0,A1),A1,"")

    Edit If you had merely wished to identify in ColumnA those not present in ColumnB Conditional Formatting might suit, with a formula such as =ISNA(MATCH(A1,B:B,0)>0).