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