Any chance of some help with this please ?

I am trying to sort through our companies phone bills and have so far got the data in from the online phone bill and pasted it in, I have grouped it into bunches of the same number etc.

I am trying to allocate a tag of either "personal" or "work" to the phone numbers.


=SUBSTITUTE(D16, "5552345", "Private")

this line replaces the numbers 5552345 with the text private - this is easy.

how can I get the line to search throgh a list of numbers and tag it either private ot work,

something like this, but the line is wrong:


=SUBSTITUTE(D16, "5552345,901,354474,8478464,363478347", "Private")(D16," 999,123,192"Work")

cheers,

mike.

 

if this was me, i would try the IF command, so if(number)=334455,true(private),false(work)

I dont know if yu can link the if command with statements like, =334455 OR 445566 etc, but this might work

Gareth

 

Set up a range of numbers (column) that you want to be classified as private and name it PrivateNos.

For each record on your list add the vlookup function.
E.g. Say column A has the numbers, in column B add this formula:
(Place this example in cell B2)
=if(isna(vlookup(A2,PrivateNos,1,false)),"Work","P rivate")

This works by evaluating the number against the list. If the vlookup function cannot return a value the ISNA part returns WORK, else it is PRIVATE.

Rgds,

Martin.

 

have a separate table of known numbers e.g.

123245 Private
6235724 Private
1275057 Work

name this table "NUMBERS" (insert->name->define)

Then do a vlookup against the original list

if you have "123245" in A1 then in A2 have "=vlookup(+A1,NUMBERS,2,FALSE)" which would return "Private".

Deano