Escape whitespaces in formula strings

**Carsti** · 02-09-2016, 12:57 PM

Dear helpful people of this forum.

Let's assume we have cells that contains strings like "-a -bcd -x -xy -xyz" or any combination of those.
I now need to count occurrences of say "-x". Currently I'm doing that with a formula like:

=COUNTIF(RawData!C2:C99999;"*-x*")

Unfortunately that counts "-x" "-xy" and "-xyz" regardless. So I tried

=COUNTIF(RawData!C2:C99999;"*-x *")

but that yields the same result, so I assume the space after "-x " is ignored. Same goes for regular text filters one can use via UI.
Is there any way to enforce "-x" and only that, like "-xwithatrailingspace"

Thanks in advance.
//Carsten

**sourabhg98** · 02-09-2016, 01:00 PM

Please Login or Register  to view this content.

This should work fine. It is working for me.

Can you attach a smaple workbook.
You can do so by clicking on "Go Advanced" button at the bottom of the post and then selecting the "Paper Clip" icon from the tools above.

**Tony Valko** · 02-09-2016, 01:02 PM

Post some sample data and tell us what result you expect.

**Pepe Le Mokko** · 02-09-2016, 01:03 PM

Why not just =COUNTIF(RawData!C2:C99999;"*-x")

**sourabhg98** · 02-09-2016, 01:13 PM

Originally Posted by Pepe Le Mokko

Why not just =COUNTIF(RawData!C2:C99999;"*-x")

But I think that won't accept anything after "-x".
So it recognizes "abc-x" but not "abc-x -def"

**ChemistB** · 02-09-2016, 01:34 PM

COUNTIF( range, "*-x *") should work as long as
1. there's never a "-" right after the x and
2. the "-x" doesn't occur right at the end (as discussed by Pepe and Sourabhg)

**sourabhg98** · 02-09-2016, 01:37 PM

Please Login or Register  to view this content.

This would cover every situation I think.

**Carsti** · 02-10-2016, 03:04 AM

Thanks for all the answers. I think in reading them and in preparing the requested sample I found my error.

First off all it's not a good idea to directly go for a 100K row sheet with huge variety, one can't really verify the results.

Anyway, I think I have to go for something like:

=COUNTIF(A2:A8;"*-x *")+COUNTIF(A2:A8;"*-x")

as my error seemed to be not understanding how "ends with" comes into play. See attached sample.

countif.xlsx
Calculation in row 10 obviously counts too much as it also include row 6 for obvious reason.
My next try in row 11 fails to cover for cells ending in "-x"
Only row 12 yields the correct result, so I have to enhance all calculation.
Sounds reasonable but not really fun.

Or do you see any issues with that approach?

Thanks, Carsten

**FlameRetired** · 02-10-2016, 03:28 AM

Try array entering this in B2 and filling down to get counts at each row.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulae array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

For the total count in column A try array entering this.

Formula:

Please Login or Register  to view this content.

**FlameRetired** · 02-10-2016, 03:40 AM

BTW the ROW($1:$20) was arbitrary. A larger number might be in order ROW($1:$30) ... or better yet replace the ROW functions with ROW(INDIRECT("1:"&LEN($A2:$A8))).
That way you will always be certain to catch all the characters in column A like this

Formula:

Please Login or Register  to view this content.

Array entered again.

**hemesh** · 02-10-2016, 05:32 AM

may be below count all instances of -X in c2
=SUMPRODUCT(--(NOT(ISERR(SEARCH("-X ",A2:A8&" ")))))

or
=SUM(COUNTIF(A2:A8,{"*-x *","*-x"}))

**Carsti** · 02-10-2016, 12:04 PM

Thanks for the additional answers. I will not pretend that I have fully understood the row part, but of course the orginal excel has many more columns, so I'm not sure if that influences the performance. That's already an issue with 100K+ rows. I can not upload the original data, though. Confidential and stuff.

But I do love the =SUM(COUNTIF(A2:A8,{"*-x *","*-x"})) notation for clarity. Didn't even knew about the curly bracket notation. Way cool.

Big thumbs up to all of you.

**hemesh** · 02-10-2016, 12:38 PM

You are welcome and Thanks for the feedback

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

I would appreciate if you consider clicking add rep icon below the bottom left corner of the post of all who have been a help to you, this adds to the reputation of user.

**hemesh** · 02-13-2016, 11:34 AM

Thanks for the rep

Escape whitespaces in formula strings

LinkBack

Thread Tools

Rate This Thread

Display

Escape whitespaces in formula strings

Re: Escape whitespaces in formula strings

Re: Escape whitespaces in formula strings

Re: Escape whitespaces in formula strings

Re: Escape whitespaces in formula strings

Re: Escape whitespaces in formula strings

Re: Escape whitespaces in formula strings

Re: Escape whitespaces in formula strings

Re: Escape whitespaces in formula strings

Re: Escape whitespaces in formula strings

Re: Escape whitespaces in formula strings

Re: Escape whitespaces in formula strings

Re: Escape whitespaces in formula strings

Re: Escape whitespaces in formula strings

Thread Information

Users Browsing this Thread

Similar Threads

Disable Escape Key

excel formula to search Multiple strings in several columns and return strings

excel formula to search Multiple strings in several columns and return strings in a separa

escape causes error

Whitespaces created in multi-child elements when using Excel to open XML file

Escape key for '

Escape My Debugger!

Bookmarks

Bookmarks

Posting Permissions