CODEWITHSUNDEEP
excelif-statementexcel-formulaexcel-2007vlookup
user2645113
user2645113

Reputation: 83

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?

Upvotes: 0

Views: 330

Answers (2)

pnuts
pnuts

Reputation: 59485

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

Upvotes: 1

teylyn
teylyn

Reputation: 35915

You may want to post a data sample. Does column B have the actual numbers? Or a flag to remove the number of the same row?

Consider:

enter image description here

To build a contiguous list of numbers to be removed, add a helper column with the formula

=IF(ISBLANK(B2),"",ROW())

in cell C2 and copy down. Then enter this formula into cell D2:

=IFERROR(INDEX(A:A,SMALL(C:C,ROW(A1))),"")

Copy down. The result is:

enter image description here

In this setup, it does not really matter what the values in column B are. It can be a "yes" or a "delete" or the actual phone number. The important bit is that the cells in column B are either empty or have a value (any value).

Upvotes: 0

Related Questions