ExcelBanter - Ignoring everthing after a hypen

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

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Ignoring everthing after a hypen (https://www.excelbanter.com/excel-discussion-misc-queries/44684-ignoring-everthing-after-hypen.html)

Ignoring everthing after a hypen

In a sumproduct formula I have a criteria that is separated by a "-".

Example:

20001-345
20001-456
20001-500

I want to include all numbers that start with 20001 in the criteria
regardless of anything that follows it. Do I convert it to text and
surround 20001 in " "s?

Thanks for any and all help!!

Steve

Hi!

What's your formula actually doing?

You could do both:

LEFT(A1,5)="20001"

OR

LEFT(A1,5)*1=20001

OR

--LEFT(A1,5)=20001

OR

ISNUMBER(SEARCH(20001,A1))

Biff

"Steve" wrote in message
...
In a sumproduct formula I have a criteria that is separated by a "-".

Example:

20001-345
20001-456
20001-500

I want to include all numbers that start with 20001 in the criteria
regardless of anything that follows it. Do I convert it to text and
surround 20001 in " "s?

Thanks for any and all help!!

Steve

My formula is this...where 2155 the criteria I want to include despite the
fact that in Column C there are multiply variations of that number such
as:2155-145, 2155-347, etc...... sometimes (this sheet is part of 110 other
sheets and they are filled out by various people sooooooo.... sometimes they
put 2155.145 and sometimes they put in a space between the hyphens so there
are many variations.... that I would like to include as long as they start
with 2155.

=SUMIF(C4:C40,2155,D4:D40)

Does this info change things? I know in searching for things you can put
*.* to do this but it doesn't seem to work here.

Thanks a million for your help

"Biff" wrote:

Hi!

What's your formula actually doing?

You could do both:

LEFT(A1,5)="20001"

OR

LEFT(A1,5)*1=20001

OR

--LEFT(A1,5)=20001

OR

ISNUMBER(SEARCH(20001,A1))

Biff

"Steve" wrote in message
...
In a sumproduct formula I have a criteria that is separated by a "-".

Example:

20001-345
20001-456
20001-500

I want to include all numbers that start with 20001 in the criteria
regardless of anything that follows it. Do I convert it to text and
surround 20001 in " "s?

Thanks for any and all help!!

Steve

Hi!

OK, since some entries may be numeric - 2155.145, and some may be text -
2155-145, Sumif won't work (even with wildcards), so your best choice is:

=SUMPRODUCT(--(LEFT(C4:C40,4)="2155"),D4:D40)

Biff

"Steve" wrote in message
...
My formula is this...where 2155 the criteria I want to include despite the
fact that in Column C there are multiply variations of that number such
as:2155-145, 2155-347, etc...... sometimes (this sheet is part of 110
other
sheets and they are filled out by various people sooooooo.... sometimes
they
put 2155.145 and sometimes they put in a space between the hyphens so
there
are many variations.... that I would like to include as long as they start
with 2155.

=SUMIF(C4:C40,2155,D4:D40)

Does this info change things? I know in searching for things you can put
*.* to do this but it doesn't seem to work here.

Thanks a million for your help

"Biff" wrote:

Hi!

What's your formula actually doing?

You could do both:

LEFT(A1,5)="20001"

OR

LEFT(A1,5)*1=20001

OR

--LEFT(A1,5)=20001

OR

ISNUMBER(SEARCH(20001,A1))

Biff

"Steve" wrote in message
...
In a sumproduct formula I have a criteria that is separated by a "-".

Example:

20001-345
20001-456
20001-500

I want to include all numbers that start with 20001 in the criteria
regardless of anything that follows it. Do I convert it to text and
surround 20001 in " "s?

Thanks for any and all help!!

Steve

Hi!

Another thing that you could do is "force" all users to enter data in a
uniform style!

Maybe setup data validation that will only accept decimal values. Data
validation is easily "defeated", though!

Biff

"Biff" wrote in message
...
Hi!

OK, since some entries may be numeric - 2155.145, and some may be text -
2155-145, Sumif won't work (even with wildcards), so your best choice is:

=SUMPRODUCT(--(LEFT(C4:C40,4)="2155"),D4:D40)

Biff

"Steve" wrote in message
...
My formula is this...where 2155 the criteria I want to include despite
the
fact that in Column C there are multiply variations of that number such
as:2155-145, 2155-347, etc...... sometimes (this sheet is part of 110
other
sheets and they are filled out by various people sooooooo.... sometimes
they
put 2155.145 and sometimes they put in a space between the hyphens so
there
are many variations.... that I would like to include as long as they
start
with 2155.

=SUMIF(C4:C40,2155,D4:D40)

Does this info change things? I know in searching for things you can put
*.* to do this but it doesn't seem to work here.

Thanks a million for your help

"Biff" wrote:

Hi!

What's your formula actually doing?

You could do both:

LEFT(A1,5)="20001"

OR

LEFT(A1,5)*1=20001

OR

--LEFT(A1,5)=20001

OR

ISNUMBER(SEARCH(20001,A1))

Biff

"Steve" wrote in message
...
In a sumproduct formula I have a criteria that is separated by a "-".

Example:

20001-345
20001-456
20001-500

I want to include all numbers that start with 20001 in the criteria
regardless of anything that follows it. Do I convert it to text and
surround 20001 in " "s?

Thanks for any and all help!!

Steve

Thanks Biff!!

"Biff" wrote:

Hi!

OK, since some entries may be numeric - 2155.145, and some may be text -
2155-145, Sumif won't work (even with wildcards), so your best choice is:

=SUMPRODUCT(--(LEFT(C4:C40,4)="2155"),D4:D40)

Biff

"Steve" wrote in message
...
My formula is this...where 2155 the criteria I want to include despite the
fact that in Column C there are multiply variations of that number such
as:2155-145, 2155-347, etc...... sometimes (this sheet is part of 110
other
sheets and they are filled out by various people sooooooo.... sometimes
they
put 2155.145 and sometimes they put in a space between the hyphens so
there
are many variations.... that I would like to include as long as they start
with 2155.

=SUMIF(C4:C40,2155,D4:D40)

Does this info change things? I know in searching for things you can put
*.* to do this but it doesn't seem to work here.

Thanks a million for your help

"Biff" wrote:

Hi!

What's your formula actually doing?

You could do both:

LEFT(A1,5)="20001"

OR

LEFT(A1,5)*1=20001

OR

--LEFT(A1,5)=20001

OR

ISNUMBER(SEARCH(20001,A1))

Biff

"Steve" wrote in message
...
In a sumproduct formula I have a criteria that is separated by a "-".

Example:

20001-345
20001-456
20001-500

I want to include all numbers that start with 20001 in the criteria
regardless of anything that follows it. Do I convert it to text and
surround 20001 in " "s?

Thanks for any and all help!!

Steve