ExcelBanter - "Begins with"

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - "Begins with" (https://www.excelbanter.com/excel-worksheet-functions/144172-begins.html)

Amber

"Begins with"

How can I express the following within a function?

begins with
contains
ends with

Peo Sjoblom

"Begins with"

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

--
Regards,

Peo Sjoblom

"Amber" wrote in message
...
How can I express the following within a function?

begins with
contains
ends with

[email protected]

"Begins with"

On May 25, 10:59 am, Amber wrote:
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.

Mike H

"Begins with"

Amber,

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

Mike

"Amber" wrote:

How can I express the following within a function?

begins with
contains
ends with

T. Valko

"Begins with"

Like this:

A1 = XXXabcZZZ

begins with

=LEFT(A1,3)="XXX"

contains

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

ends with

=RIGHT(A1,3)="ZZZ"

Note that none of these are case sensitive.

Biff

"Amber" wrote in message
...
How can I express the following within a function?

begins with
contains
ends with

[email protected]

"Begins with"

Dave Peterson

"Begins with"

As worksheet functions?

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

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

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

Amber wrote:

How can I express the following within a function?

begins with
contains
ends with

--

Dave Peterson

Amber

"Begins with"

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

" wrote:

On May 25, 10:59 am, Amber wrote:
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.

Amber

"Begins with"

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

"Amber" wrote:

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

" wrote:

On May 25, 10:59 am, Amber wrote:
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.

T. Valko

"Begins with"

Maybe something like this:

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

Biff

"Amber" wrote in message
...
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

" wrote:

On May 25, 10:59 am, Amber wrote:
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.

Amber

"Begins with"

What do A and B Stand for?

"T. Valko" wrote:

Maybe something like this:

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

Biff

"Amber" wrote in message
...
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

" wrote:

On May 25, 10:59 am, Amber wrote:
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.

Peo Sjoblom

"Begins with"

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

--
Regards,

Peo Sjoblom

"Amber" wrote in message
...
What do A and B Stand for?

"T. Valko" wrote:

Maybe something like this:

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

Biff

"Amber" wrote in message
...
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

" wrote:

On May 25, 10:59 am, Amber wrote:
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.

T. Valko

"Begins with"

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

"Amber" wrote in message
...
What do A and B Stand for?

"T. Valko" wrote:

Maybe something like this:

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

Biff

"Amber" wrote in message
...
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

" wrote:

On May 25, 10:59 am, Amber wrote:
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.

All times are GMT +1. The time now is 04:04 AM.