On further investigation you are correct that IE automatically destroys those spaces. Also I think he has copied his detailed list and then deleted the additional comments to get his plain list, but he has left the spaces on some.
The end result is that you will either have try and get people to improve their formatting, use IE, use the IE Tab, or stuff around trying to get Excel to work with the variations.
You can use the following formula in Excel to remove the trailing spaces.
=IF(RIGHT(A1,1)=" ",LEFT(A1,LEN(A1)-1),A1)
Where “A1” is the cell that the offending text resides in. It will overlook text that does not have a trailing space.
Probably easier to just use IE though :(.
Edit: Hang on! What’s this in the Google Chrome options under “Copy Settings”? … just kidding, sorry.
Back when I was doing web design for a quasi-living, I had on my computer every single browser you could think of, FF (my main browser), IE (many different versions, at least half a dozen going back to 3.0 (ah, memories!)), Opera, Chrome, even Netscape. The reason for this was so I could check my final work in all browsers and if it displayed correctly there, chances are it woudl display correctly on my client’s configuration too. A side benefit was that I would use IE for those stubborn IE-only sites (only one I can think of that I go to with any regularity, the website I go to to check my work email from home) and still use FF for everything else.
I have one sheet with everyone’s lists in. I copy & paste the list of names into that sheet (the List sheet). I have a second sheet with just a list of all the names (the Name sheet).
When I copy a new person’s list into the List sheet, it tells me against each name how many times that celebrity has been nominated. If that number is a “1”, I know this is a new celebrity. I then copy all new celebrities to the Name sheet.
When a celebrity dies, I put their age at death in the Name sheet, which calculates the points scored for nominating that celebrity. For every occurrence of that name in the List sheet, the number of points is added to the total points for the person who named the celebrity.
Hence there is a lot of comparing across lists and sheets. I never actually reference the text in another cell, and hence cannot do the string functions you suggest other than if I paste the cells in in another place, then reference them as you suggest. I am using COUNTIF and SUMIF, and the comparisons have to be identical - I cannot have one list with a space at the end of names and the other not.
(I also have a third sheet that calculates the positions of the players, but that does not compare celebrity names anywhere.)
But thanks for trying to help me out.
Yeah no worries. Sometimes a program just isn’t right for you. I’ve been trying to get myself off of Outlook and on to gmail for a while but I just don’t like the gmail interface.
Click the spanner icon at the top right of the screen, select “options”, then under the “Personal stuff” tab click on “clear browsing history”. You are then given a range of things to delete including the cache.