How do I filter cells that end with a space?

[mpsnoop6]

Hi,

Is this possible? I have thousands of names in one column and I'm trying to count them using =countif. Problem is, there's something wrong with the data and some of the names end in a space. My count becomes inaccurate since they count the ones with spaces separately.

EX:
mpsnoop
mpsnoop(space)

By right, the count should be 2 but since one has a space, it counts 1 and 1. I can't use text to columns either because some of the names are full names with spaces in between (ie mpsnoop snoop)

Any help is greatly appreciated. Thanks!

Regards,
mpsnoop6

[bebo021999]

Try SUMPRODUCT:
=SUMPRODUCT((TRIM($A$1:$A$100)=$B$1)*1)
With your given name in B1 and your name data in A1:A100

[Kyle123]

like this, there's probably a better way however:

=SUM(IF(SUBSTITUTE(A1:A10," ","")="mpsnoop",1))  

or

=SUM(IF(TRIM(A1:A10," ","")="mpsnoop",1))  

This is an array formula so entered with Ctrl+Shift+Enter

[ExcelHelp88]

To get rid of spaces before and after use =trim(A1). Then use this new column for what you want to do.

[Kyle123]

That's what the above functions do they're both array formulas which means that you don't have to have a separate helper column with trimmed values

[Cutter]

A simple, non-array option would be:

Please Login or Register  to view this content.

where B1 holds the name to be counted

[Pete_UK]

You can use wildcards with COUNTIF, so try it like this:

=COUNTIF(A:A,B1&"*")

where B1 contains your name without the space.

Hope this helps.

Pete

[Cutter]

I was going to suggest the wildacrd approach but it may lead to problems, like:

Bill Smith as the name to be counted

so using: =COUNTIF(A:A,"Bill Smith*")

Bill Smithers would also be counted

[martindwilson]

assuming it IS a space and not char(10),char(0160) or such
select column
data /text to columns
fixed width
remove all the break marks
click finish
bye bye leading and trailing spaces

[mpsnoop6]

Hi all,

Wow thanks for all the replies. Got everything I need now. Thanks again!

Regards,
mpsnoop6

[Cutter]

You're welcome. Please remember to mark your thread as SOLVED.

Here's how:

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save