Unable to get rid of extra spaces in cells

[WVUfan]

Firstly, thank you for taking the time out to help me.

I'm working on a project where I'm attempting to track different football players' performance over time. I currently have different sheets for each year. So, what I'm currently trying to do is track which players stayed on the team over multiple years.

The issue is that when I was copying the data from online there were random spaces entered after some players' names. So I've taken all the names and put them into a single sheet, but when I enter the following formula to check to see if a player was on the roster for two years, some do not show up because they have a space after their name in one of the years, but not the other: =IF(ISERROR(MATCH(A2,$B$2:$B$1500,0)),"",A2)

I went through one of the years manually to delete the extra spaces, but this took about a half hour (lots of rosters), and it's quite tedious. The TRIM function did not seem to work when trying to delete the space after the names. It ended up reverting back to a space as soon as I copied and pasted the output from it. I've tried COUNTIF to see if I could get a value back for a cell with more than one space. Everything comes back the same, and I assume it's because there are words in between the spaces, but I'm not positive.

Anything that could make this process more efficient than just going through it manually would be of great assistance. I appreciate it! Let me know if I need to make my question more clear or use an example.

 

[SpillerBD]

The "Space" is probably not a space but carriage return (or other non-printing character) which TRIM does not get rid of.
Try using the TEXT TO Colums with a custom delimeter. Type ALT+0010 for the delimeter text.

You can also try using Find & Replace.
You can verify the character in your original text using

=CODE(RIGHT(A1,1))

 

[Tetra201]

Try this for getting cleaned names in a helper column:

=TRIM(SUBSTITUTE(A1,CHAR(160)," "))

 

[Rick Rothstein]

My guess is that some of your data was obtained by copy/pasting from a website. If so, then some of those "spaces" are probably not regular spaces (ASCII 32) but rather are non-breaking spaces (ASCII 160). Let's convert them to normal spaces so that the TRIM function will work on them. To do this, select all of the columns that contain data giving you trouble (or simply select all of the cells) and press CTRL+H to bring up Excel's Replace dialog box. Clear the "Find what" field (if anything is in it) and then with that field having focus, hold down the ALT key while you type 0160 using only the Number Pad (not the number keys on the main keyboard) then release the ALT key. Next, put a single space character in the "Replace With" field. Next click the "Options>>" button and make sure there is no checkmark in the checkbox labeled "Match entire cell contents". Finish off by clicking the "Replace All" button. Now all of your spaces are real spaces and the TRIM function will work on them.

 

[Leith Ross]

Hello WVUfan,

Web pages use 2 types of spaces: White space (Character 32) and Non-Breaking space (Character 160).

You can remove leading and trailing spaces, multiple spaces, and non-breaking spaces using this formula: [FONT=&quot]=TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32)))[/FONT]

 

[Snakehips]

I tend to use this to do a thorough clean..

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),"")))

Eg. use formula in column B to give clean version of column A

Then Copy the results in B and Paste Special > Values either to another column or in place in Column B.

That removes the formulas and leaves you with 'clean' hard data.

Hope that helps.

 

[Tetra201]

@Snakehips:

Apparently you meant " ", not "". Otherwise it will return "JohnDoe" instead of "John Doe".

 

[Snakehips]

@Snakehips:

Apparently you meant " ", not "". Otherwise it will return "JohnDoe" instead of "John Doe".

Good spot. Thank you.