Using search function to classify data
 

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(S EARCH("ABT",C5697)))),VALUE(RIGHT(C5697,7)),IF(ISN UMBER(SEARCH("Rental",C5697)),RIGHT(C5697,8),IF(OR ((ISNUMBER(SEARCH("ABC",C5697))),(ISNUMBER(SEARCH( "ABB",C5697))),(ISNUMBER(SEARCH("AAC",C5697))),(IS NUMBER(SEARCH("CH",C5697))),(ISNUMBER(SEARCH("TAX" ,C5697))),(ISNUMBER(SEARCH("GD",C5697)))),"EXPENSE ",IF(OR(ISNUMBER(SEARCH("CASH",C5697)),ISNUMBER(SE ARCH("-",C5697))),"DEPOSIT",IF(OR((ISNUMBER(SEARCH("12345 67",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

Using search function to classify data
 

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.

--
Biff
Microsoft Excel MVP


"Fanny" wrote in message
...
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(S EARCH("ABT",C5697)))),VALUE(RIGHT(C5697,7)),IF(ISN UMBER(SEARCH("Rental",C5697)),RIGHT(C5697,8),IF(OR ((ISNUMBER(SEARCH("ABC",C5697))),(ISNUMBER(SEARCH( "ABB",C5697))),(ISNUMBER(SEARCH("AAC",C5697))),(IS NUMBER(SEARCH("CH",C5697))),(ISNUMBER(SEARCH("TAX" ,C5697))),(ISNUMBER(SEARCH("GD",C5697)))),"EXPENSE ",IF(OR(ISNUMBER(SEARCH("CASH",C5697)),ISNUMBER(SE ARCH("-",C5697))),"DEPOSIT",IF(OR((ISNUMBER(SEARCH("12345 67",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

Using search function to classify data
 

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


"Fanny" wrote:

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(S EARCH("ABT",C5697)))),VALUE(RIGHT(C5697,7)),IF(ISN UMBER(SEARCH("Rental",C5697)),RIGHT(C5697,8),IF(OR ((ISNUMBER(SEARCH("ABC",C5697))),(ISNUMBER(SEARCH( "ABB",C5697))),(ISNUMBER(SEARCH("AAC",C5697))),(IS NUMBER(SEARCH("CH",C5697))),(ISNUMBER(SEARCH("TAX" ,C5697))),(ISNUMBER(SEARCH("GD",C5697)))),"EXPENSE ",IF(OR(ISNUMBER(SEARCH("CASH",C5697)),ISNUMBER(SE ARCH("-",C5697))),"DEPOSIT",IF(OR((ISNUMBER(SEARCH("12345 67",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