"Begins with"

[Guest]

How can I express the following within a function?

begins with
contains
ends with

 

[Peo Sjoblom]

It depends what kind of function? Which one are you thinking of?

 

[omnicrondelicious]

How can I express the following within a function?

begins with
contains
ends with

I think you are looking for the LEFT, FIND, and RIGHT functions. Not
sure exactly how you want to use these but here's some examples:

Does the cell A1 begin with "Bob"?
=LEFT(A1,3)="Bob"

Does the cell A1 contain "Bob"?
=FIND("Bob",A1)>0

Does the cell A1 end with "Bob"?
=RIGHT(A1,3)="Bob"


If you want to refer to another cell, say B1, then it's:
=LEFT(A1,LEN(B1))=B1
=FIND(B1,A1)>0
=RIGHT(A1,LEN(B1))=B1


cheers,
..o.

 

[Guest]

Amber,

If you mean a UDF then you can use the LIKE function in VBA but you need to
be more specific.

Mike

 

[T. Valko]

Like this:

A1 = XXXabcZZZ

begins with
=LEFT(A1,3)="XXX"


=ISNUMBER(SEARCH("abc",A1))

ends with

=RIGHT(A1,3)="ZZZ"

Note that none of these are case sensitive.

Biff

 

[omnicrondelicious]

How can I express the following within a function?

begins with
contains
ends with

I think you are looking for the LEFT, FIND, and RIGHT functions. Not
sure exactly how you want to use these but here's some examples:

Does the cell A1 begin with "Bob"?
=LEFT(A1,3)="Bob"

Does the cell A1 contain "Bob"?
=FIND("Bob",A1)>0

Does the cell A1 end with "Bob"?
=RIGHT(A1,3)="Bob"


If you want to refer to another cell, say B1, then it's:
=LEFT(A1,LEN(B1))=B1
=FIND(B1,A1)>0
=RIGHT(A1,LEN(B1))=B1


cheers,
..o.

 

[Dave Peterson]

As worksheet functions?

Begins with:
=countif(a1,"Amber*")>0

contains:
=countif(a1,"*amber*")>0

ends with:
=countif(a1,"*amber")>0

 

[Guest]

What I am trying to do is do a sumif statement but there are some of the the
line items that I want to not be added. I need to try to figure out how to
say that in an expression.... Any help you could give would be great.

Thanks Amber

 

[Guest]

Sorry I forgot to add that the items items to exclude would all start
with"600" but are all different.

 

[T. Valko]

Maybe something like this:

=SUMPRODUCT(--(LEFT(A1:A10,3)<>"600"),B1:B10)

Biff

 

[Guest]

What do A and B Stand for?

Maybe something like this:

=SUMPRODUCT(--(LEFT(A1:A10,3)<>"600"),B1:B10)

Biff

 

[Peo Sjoblom]

Cell references, replace them with the ranges you want to apply this on

 

[T. Valko]

A1:A10 is the range of cells that contain your line items.
B1:B10 is the range to sum.

Maybe you need to post a more descriptive explanation.

Biff