Using search function to classify data

F

Fanny

Dear Helpers,

Via e-banking service, I have extracted my current account data to control
my business. From the data, I use the following formula to classify the
payment types based on the transaction description.

=IF(OR((ISNUMBER(SEARCH("AAW",C5697))),(ISNUMBER(SEARCH("ABT",C5697)))),VALUE(RIGHT(C5697,7)),IF(ISNUMBER(SEARCH("Rental",C5697)),RIGHT(C5697,8),IF(OR((ISNUMBER(SEARCH("ABC",C5697))),(ISNUMBER(SEARCH("ABB",C5697))),(ISNUMBER(SEARCH("AAC",C5697))),(ISNUMBER(SEARCH("CH",C5697))),(ISNUMBER(SEARCH("TAX",C5697))),(ISNUMBER(SEARCH("GD",C5697)))),"EXPENSE",IF(OR(ISNUMBER(SEARCH("CASH",C5697)),ISNUMBER(SEARCH("-",C5697))),"DEPOSIT",IF(OR((ISNUMBER(SEARCH("1234567",C5697))),(ISNUMBER(SEARCH("23456789",C5697))),(ISNUMBER(C5697))),C5697,"TRANSFER")))))

As the description changes from time to time according the bank's operation
and the "nested If" problem, I am looking for any solution to use "search"
function like vlookup a listing.

Thanks in advance your kind assistance.

Fanny
 
T

T. Valko

That's one ugly formula you got there! <g>

Can you show us some examples of what cell C5697 might contain? That'll give
us a better idea of what you're looking for.

We should be able to clean up that formula a bit.
 
F

Fanny

Dear Helpers,

the data in the corresponding field are similar to the following:

Transaction Description
AAW 14233301
ABT 1423302
1423303
1423304 RENTAL 1
ABC 90000001
ABB 80000002
CASH Deposit
122222-56789
1234567
98765432
For those unclassified

Thanks for your help.

Fanny
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top