MrExcel Publishing
Your One Stop for Excel Tips & Solutions

extracting part of a code from a cell & using it in a formula


Posted by Jeannette on January 27, 2002 2:59 AM

I have a cell with a code ex: (LMDE or MDF) and each letter represents a code in order to calculate based on the letter. The problem is the letter is never in the same location when I look for it in the cell, how can I search the cell and if it finds the letter I am looking for then perform my calculation? I have tried using the left() function and I can't seem to get it to continue searching to the next postion until found. I hope this isn't confusing and someone can help, thanks!
Jeannette


Posted by Aladin Akyurek on January 27, 2002 3:54 AM

Jeannette --

Not sure you'll going to answer the info questions that follow. I'll pose them all the same.

Are you searching for a particular letter in a cell or in a range of cells?

If you find the search letter, what happens next?

Please elaborate.

Aladin

=========

Posted by Jeannette on January 27, 2002 4:05 AM

Yes, in the cell the letters represent what I am to calculate, ex: L= Life insurance rates...if found I enter the formula for the rates. Thank you!

--


Posted by Aladin Akyurek on January 27, 2002 4:28 AM

OK.

Lets say that you have a string consisting of relevant letters in A1.

I'll assume that when a particular letter code found, you activate a relevant formula (not "enter" as you said in your example: "L= Life insurance rates...if found I enter the formula for the rates.")

If you have formulas corresponding to letter codes in different cells, e.g., a formula that runs when L found is in B1, a formula that runs when M found in B2, etc., you can add the following to the formula in B1:

=IF(ISNUMBER(SEARCH("L",A1)),the-formula-bit-that-does-rates,"")

or, written differently and much nicer,

=(ISNUMBER(SEARCH("L",A1)))*(the-formula-bit-that-does-rates)

Hope this helps.

=========