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? Thanks for your help.

---

Answer from Richard Rost:

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.