Complex formula - hope you can help

QuestionEdit

QUESTION: Hey there,

I would need a formula or a macro that would have the ability to recognize specific text in a column and assign the text a value to be counted up and the final numbers to be displayed.

For Example if I had a column that states the following

HALFBATH:1;FP:1;AC:SPLIT SYSTEM;POOL:CONCRETE;Sewer:PUBLIC;

I need a formula/macro that would be able to recognize "HALFBATH" and give it a value of 1 for this example. It would recognize "AC:SPLIT SYSTEM" assign it a value of 2. Assign "POOL" a value of 3, etc. These values would then be added together to display a value, in this case, 6 in an adjacent column. If you could get me started I think I can run with it. I would need this to be expandable to more text & values. Is something like this do-able? ANSWER: I wouldn't try to do this in one formula, as it will get crazy, but you could break it up into multiple columns, one for each keyword you're trying to find, and then add up all of the values.

Here's an example. Put the following values in these cells:

B1: SYSTEM B2: 5 C1: POOL C2: 7

Those are your keywords and their values. Now, put the long code you're searching for in A4:

SOMETHING;POOL;

Now in B4, you can look to see if "SYSTEM" is in A4. If so, use your assigned value.

=IF(IFERROR(FIND(B1,$A$4),0),B2,0)

Do the same for C4:

=IF(IFERROR(FIND(C1,$A$4),0),C2,0)

Now you can add all of these values up across the row, and that should give you the value you want.

Hope this helps get you started.

---------- FOLLOW-UP ----------

QUESTION: The problem with that, is that, this is something I need to do to an existing spreadsheet. Iscapable of recognizing text within a column while disregarding some text? The column that I described above has numerous combinations of HALFBATH:1;FP:1;AC:SPLIT SYSTEM;POOL:CONCRETE;Sewer:PUBLIC;. Sometimes the values are repeated in the same order. Would I have to create a specific If/Then for each possible combination? Could I instead export that section to anotherfile, save it as a .txt file, re-open as a CSV (so all the values are seperated) then use a single formula for each column totaling them up.

AnswerEdit

Yes, you'd have to create a separate column to check for "POOL:CONCRETE" and then "POOL:VINYL". This might not be the BEST way to do it, but it will work.

The BEST way to handle this would be to break down every variable and store the information in a database like Microsoft Access. That will probably require some programming (to parse your data into something usable), but it will give you the best end result.