I have a big ugly spreadsheet dumped from an external nondelimited file.

18 columns by 72000 lines (two files)... so...

In one cell every so often there is a piece of data that I need to head the
next two, three, four, five lines below it. It will ALWAYS be preceded by
text "ING1AR", but "ING1AR" will not necessarily be the first six characters
in the cell.

I know I can filter for that, but that won't let me see the data that falls
in the lines below it.

I was hoping to create a column that basically said, IF that cell contains
"ING1AR", tell me everything that comes after that.

I know if I do "MID(cell,FIND...." I can pull out what comes after the text,
but not how to combine that with an IF statement...

Does that make sense?

If your data is in B1:Bxxx, then you could use a formula like this in A2.

=if(or(countif(b1,"*ing1ar*")0,countif(b2,"*ingla r*")0),"Show it","hide it")

Then you could filter on this helper column.

RJB wrote:

I have a big ugly spreadsheet dumped from an external nondelimited file.

18 columns by 72000 lines (two files)... so...

In one cell every so often there is a piece of data that I need to head the
next two, three, four, five lines below it. It will ALWAYS be preceded by
text "ING1AR", but "ING1AR" will not necessarily be the first six characters
in the cell.

I know I can filter for that, but that won't let me see the data that falls
in the lines below it.

I was hoping to create a column that basically said, IF that cell contains
"ING1AR", tell me everything that comes after that.

I know if I do "MID(cell,FIND...." I can pull out what comes after the text,
but not how to combine that with an IF statement...

Does that make sense?

--

Dave Peterson

I'll experiment with countifs and *... didn't know you could use that as a
wildcard in formulas.

Filtering is kind of my last resort.

Thanks!

"Dave Peterson" wrote:

If your data is in B1:Bxxx, then you could use a formula like this in A2.

=if(or(countif(b1,"*ing1ar*")0,countif(b2,"*ingla r*")0),"Show it","hide it")

Then you could filter on this helper column.

RJB wrote:

I have a big ugly spreadsheet dumped from an external nondelimited file.

18 columns by 72000 lines (two files)... so...

In one cell every so often there is a piece of data that I need to head the
next two, three, four, five lines below it. It will ALWAYS be preceded by
text "ING1AR", but "ING1AR" will not necessarily be the first six characters
in the cell.

I know I can filter for that, but that won't let me see the data that falls
in the lines below it.

I was hoping to create a column that basically said, IF that cell contains
"ING1AR", tell me everything that comes after that.

I know if I do "MID(cell,FIND...." I can pull out what comes after the text,
but not how to combine that with an IF statement...

Does that make sense?

--

Dave Peterson