Formula for searching for a text string
 

I have a table where each cell contains a string of various alphanumeric
codes. For example:
KB / S596-19
RM / S243-59
VB / I1997-1
KB / GEN
SV / GEN

I want to write a formula to count 1 if the cell is populated (not all cells
are), but count 0 if the cell contains 'GEN'.
I've tried this:
=IF(AND(B4<"",B4<"*GEN*"),1,0)

But the "*GEN*" portion of this formula does not work.

Any suggestions?

KellyB

Formula for searching for a text string
 

Try something like this:

=IF(ISBLANK(A1),0,IF(TRUE(FIND("GEN",A1)),0,1))

Dave


--
Brevity is the soul of wit.


"KellyB" wrote:

I have a table where each cell contains a string of various alphanumeric
codes. For example:
KB / S596-19
RM / S243-59
VB / I1997-1
KB / GEN
SV / GEN

I want to write a formula to count 1 if the cell is populated (not all cells
are), but count 0 if the cell contains 'GEN'.
I've tried this:
=IF(AND(B4<"",B4<"*GEN*"),1,0)

But the "*GEN*" portion of this formula does not work.

Any suggestions?

KellyB

Formula for searching for a text string
 

assumes gen is at the end??
helper column
=IF(AND(E2<"",RIGHT(E2,3)<"gen"),1,0)

without helper column
=SUMPRODUCT((LEN(E2:E22)2)*(RIGHT(E2:E22,3)<"gen "))

--
Don Guillett
SalesAid Software

"KellyB" wrote in message
...
I have a table where each cell contains a string of various alphanumeric
codes. For example:
KB / S596-19
RM / S243-59
VB / I1997-1
KB / GEN
SV / GEN

I want to write a formula to count 1 if the cell is populated (not all
cells
are), but count 0 if the cell contains 'GEN'.
I've tried this:
=IF(AND(B4<"",B4<"*GEN*"),1,0)

But the "*GEN*" portion of this formula does not work.

Any suggestions?

KellyB

Formula for searching for a text string
 

Sorry, do this: =IF(ISBLANK(A49),0,IF(NOT(FIND("gen",A49)),0,1))

Dave
--
Brevity is the soul of wit.


"Dave F" wrote:

Try something like this:

=IF(ISBLANK(A1),0,IF(TRUE(FIND("GEN",A1)),0,1))

Dave


--
Brevity is the soul of wit.


"KellyB" wrote:

I have a table where each cell contains a string of various alphanumeric
codes. For example:
KB / S596-19
RM / S243-59
VB / I1997-1
KB / GEN
SV / GEN

I want to write a formula to count 1 if the cell is populated (not all cells
are), but count 0 if the cell contains 'GEN'.
I've tried this:
=IF(AND(B4<"",B4<"*GEN*"),1,0)

But the "*GEN*" portion of this formula does not work.

Any suggestions?

KellyB

Formula for searching for a text string
 

Tried that, but it didn't like it when FIND("GEN",A1) returned #VALUE, i.e.
when the cell did not contain GEN.

"Dave F" wrote:

Try something like this:

=IF(ISBLANK(A1),0,IF(TRUE(FIND("GEN",A1)),0,1))

Dave


--
Brevity is the soul of wit.


"KellyB" wrote:

I have a table where each cell contains a string of various alphanumeric
codes. For example:
KB / S596-19
RM / S243-59
VB / I1997-1
KB / GEN
SV / GEN

I want to write a formula to count 1 if the cell is populated (not all cells
are), but count 0 if the cell contains 'GEN'.
I've tried this:
=IF(AND(B4<"",B4<"*GEN*"),1,0)

But the "*GEN*" portion of this formula does not work.

Any suggestions?

KellyB

Formula for searching for a text string
 

See my response to my post. My original suggestion was incorrect.
--
Brevity is the soul of wit.


"KellyB" wrote:

Tried that, but it didn't like it when FIND("GEN",A1) returned #VALUE, i.e.
when the cell did not contain GEN.

"Dave F" wrote:

Try something like this:

=IF(ISBLANK(A1),0,IF(TRUE(FIND("GEN",A1)),0,1))

Dave


--
Brevity is the soul of wit.


"KellyB" wrote:

I have a table where each cell contains a string of various alphanumeric
codes. For example:
KB / S596-19
RM / S243-59
VB / I1997-1
KB / GEN
SV / GEN

I want to write a formula to count 1 if the cell is populated (not all cells
are), but count 0 if the cell contains 'GEN'.
I've tried this:
=IF(AND(B4<"",B4<"*GEN*"),1,0)

But the "*GEN*" portion of this formula does not work.

Any suggestions?

KellyB