A
Amber
How can I express the following within a function?
begins with
contains
ends with
begins with
contains
ends with
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Peo Sjoblom
It depends what kind of function? Which one are you thinking of?
O
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.
M
Mike H
Amber,
If you mean a UDF then you can use the LIKE function in VBA but you need to
be more specific.
Mike
If you mean a UDF then you can use the LIKE function in VBA but you need to
be more specific.
Mike
T
T. Valko
Like this:
A1 = XXXabcZZZ
=RIGHT(A1,3)="ZZZ"
Note that none of these are case sensitive.
Biff
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
O
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.
D
Dave Peterson
As worksheet functions?
Begins with:
=countif(a1,"Amber*")>0
contains:
=countif(a1,"*amber*")>0
ends with:
=countif(a1,"*amber")>0
Begins with:
=countif(a1,"Amber*")>0
contains:
=countif(a1,"*amber*")>0
ends with:
=countif(a1,"*amber")>0
A
Amber
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
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
A
Amber
Sorry I forgot to add that the items items to exclude would all start
with"600" but are all different.
with"600" but are all different.
T
T. Valko
Maybe something like this:
=SUMPRODUCT(--(LEFT(A1:A10,3)<>"600"),B1:B10)
Biff
=SUMPRODUCT(--(LEFT(A1:A10,3)<>"600"),B1:B10)
Biff
A
Amber
What do A and B Stand for?
T. Valko said:Maybe something like this:
=SUMPRODUCT(--(LEFT(A1:A10,3)<>"600"),B1:B10)
Biff
P
Peo Sjoblom
Cell references, replace them with the ranges you want to apply this on
T
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
B1:B10 is the range to sum.
Maybe you need to post a more descriptive explanation.
Biff