Trimming Spaces before and after

**maurices5000** · 01-09-2008, 03:36 PM

I have a column of cells where users put in "Y" for yes and "N" for no. However, when i count the Y's and N's, if there is a space after the letter, it will not count the character. I can't depend on users to remove the spaces,they are not that computer literate. Besides spaces are hard to see.

I'm actually using the sumproduct function instead of count but i figured the method would be similar but just in case, i thought i would make sure.

How can i trim the spaces? So that "Y" will be treated the same as " Y "?

Thanks!

**VBA Noob** · 01-09-2008, 03:39 PM

Take a look at Trim under excel help

You may also want to consider setting up a dropdown list

http://www.contextures.com/xlDataVal01.html

VBA Noob

**maurices5000** · 01-09-2008, 03:44 PM

Thanks for the fast reponse. Instead of a drop down list. I think a check box might be better.

Does this require programming to retrieve the value with the sumproduct function?

Thanks!

**VBA Noob** · 01-09-2008, 03:58 PM

Does this require programming to retrieve the value with the sumproduct function?

You could link it to a cell to return true or false

http://www.bettersolutions.com/excel...T423111411.htm

or you could use a tick mark

http://www.excelforum.com/showthread...highlight=tick

VBA Noob

**maurices5000** · 01-09-2008, 04:18 PM

I found this function called trim() but it will not allow me to do
=COUNTIF(trim(A2:A5),"y") and i can't format the cell to trim automatically that i know of.