column number of 3 cell containing text in a row

[John Foley]

I am trying to find the column number of the 3rd (or nth) occurrence of text in a cell in a 1 row table using a formula. The table looks like it contains date and value entries, but they are actually all formatted as text.

i have attached the row in question.

thanks for the help.

john foley

[Jacc]

Like this?

[Glenn Kennedy]

This is a lot simpler than Jacc's solution & doesn't need a helper row. It is an ARRAY formula and must be entered with CTRL + SHIFT + ENTER

Please Login or Register  to view this content.

[samba_ravi]

find the attached file, i hope this is what you are looking for

[Glenn Kennedy]

nflsales, you could well be right in your interpretation of the OPs question. I thought he was looking for the nth occurrence of a specific piece of text, not any piece of text.

[samba_ravi]

Mr Glenn can you tell me the mean of OPs

[Glenn Kennedy]

OP = Original Poster. the person who started the thread.

[John Foley]

original poster. I am looking for the nth occurrence of any cell that contains text in a row.
john

[John Foley]

I think nflsales was closest, but i now see all the so called blank cells are filled with "", which is being read like text (i added a helper column to show this with istext).

now i think it has to be solved with either filling the blank cells with zeros, so only the ones i am looking for read as text cells, or using the len function to look for cells with a length of one or greater.

here is a new sample with the new parameters.

I can't use helper rows in the answer, because it is a long sheet imported from another file, unless i shift them below the whole import.

john

[Glenn Kennedy]

Yes, later on I did realise that that was probably what you wanted. Same formula as I used above, reset to count any non-zero value, entered as an Array Formula will work perfectly (I hope...).

Please Login or Register  to view this content.

Set with CTRL + SHIFT + ENTER & drag down

[John Foley]

i thought i would refresh this for new eyes.

[Glenn Kennedy]

Did you look at my solution at post 10? I have reattached it here (I modified your attachment to make it easier to see). Can you explain why it does not offer the solution that you wanted? Don't forget, it is an array formula & must be entered as CTRL + SHIFT + ENTER...

[John Foley]

the C14:BQ14<>0 portion of the formula kept giving me TRUE for every column no matter what variations i tried. I turned all they "" (or 0) cells to the unused naturally letter "Z" and used this formula and it worked.

=SMALL(IF(M61:CB61<>"z", COLUMN(M61:CB61)), 2)

less elegant, but it is working for my needs.

John Foley 7/6/2014 12:21:37 AM

[Glenn Kennedy]

Were you picking the equation apart and running that bit separately? If so, it may well give odd result - it works in the array equation and got the answer correctly every time in your example. Can you show me exactly how it was failing?