Applying formula to entire column

[seanppp]

I have a bunch of info in column A, and subsequent columns I want to search every cell in column A for specific phrases. So for instance:

Column A:
OG=1.064
IBU=20
SRM=7.4
...and so on.

In cell B1 I want to search for "OG=1.000". Cell B2 I want to search for "OG=1.001", and so on up to 1.300, using the following forumla:
=IF(ISNUMBER(FIND("OG=1.064",A:A)),"1.064", "0")

and so I should see a column of 300 zeros and a single non-zero value which will be the one it found in column A (in this case, 1.064).

In cell C I want to do the same but for IBU, and cell D for SRM, etc.

The problem I'm having is that in column B when I type in:
=IF(ISNUMBER(FIND("OG=1.064",A:A)),"1.064", "0"), it is for some reason not searching all of column A but only in column A in the same row the formula is put into. I don't understand this since I put A:A in the forumla, which I thought meant it searches the entire column and not a specific cell.

Any help?

[Bernie Deitrick]

Why not find that value directly? Enter this array-formula into any cell using Ctrl-Shift-Enter

=INDEX(A:A, MATCH("OG",LEFT(A1:A1000,2),FALSE))

or even the number itself, as a number:

=VALUE(MID(INDEX(A:A,MATCH("OG",LEFT(A1:A1000,2),FALSE)),4,15))

and so on....

=INDEX(A:A, MATCH("IBU",LEFT(A1:A1000,3),FALSE))
=INDEX(A:A, MATCH("SRM",LEFT(A1:A1000,3),FALSE))

[seanppp]

Thanks for the reply.

I should have mentioned that the data in column A will change all the time, it will be imported from an xml each day with different data. So in stead of looking for my OG and IBU data, etc, manually in the xml (or in column A for that matter), I'd like to make an automated set of functions that tell me, "in that column over there, this is the OG, this is the IBU, etc...

And I should mention that the data in column A actually looks like this:

<F_R_OLD_EFFICIENCY>72.0000000</F_R_OLD_EFFICIEN
<F_R_DESIRED_IBU>20.0000000</F_R_DESIRED_IBU>
<F_R_DESIRED_COLOR>10.0000000</F_R_DESIRED_COLOR
<F_R_COLOR_ADJ_STRING></F_R_COLOR_ADJ_STRING>
<F_R_DESIRED_OG>1.0524001</F_R_DESIRED_OG>
<F_R_REBALANCE_SCALE>0</F_R_REBALANCE_SCALE>

So instead of looking or Cntl-F finding IBU and then typing in 20, I'd like functions find that after <F_R_DESIRED_IBU> the number is 20.

Thanks!

[Bernie Deitrick]

For IBU, use

=VALUE(MID(INDEX(A:A,MATCH("*IBU*",A:A,FALSE)),FIND(">",INDEX(A:A,MATCH("*IBU*",A:A,FALSE)))+1,FIND("<",INDEX(A:A,MATCH("*IBU*",A:A,FALSE)),2)-FIND(">",INDEX(A:A,MATCH("*IBU*",A:A,FALSE)))-1))

This finds whatever number is between the > and <

If you want, enter IBU into cell C1, and use

=VALUE(MID(INDEX(A:A,MATCH("*" & C1 & "*",A:A,FALSE)),FIND(">",INDEX(A:A,MATCH("*" & C1 & "*",A:A,FALSE)))+1,FIND("<",INDEX(A:A,MATCH("*" & C1 & "*",A:A,FALSE)),2)-FIND(">",INDEX(A:A,MATCH("*" & C1 & "*",A:A,FALSE)))-1))

Then copy that down and enter the other values starting in C2.