=IF(ISNA(VLOOKUP(... Extra spaces kills the forumla

[haner74]

Thank you all in advance for your help on this subject. I am using the following formula.

=IF(ISNA(VLOOKUP(B19,Statistics!$B$4:$I$616,8,FALSE)),0,(VLOOKUP(B19,Statistics!$B$4:$I$616,8,FALSE)))

Right now it works correctly, with one exception.

I need to copy and paste things from the internet for the Statistics page. The trouble is that there are usually 0-2 extra spaces at the beginning of the name I am trying to get excel to look up. (This is for a hockey pool stats tracking sheet.)

For example. On one of the rosters there is D. Roy and he scored in Buffalo's game last night. But when I copy the yahoo website information it puts in 2 spaces at the front of his name and sometimes afterwards as well (**D. Roy* - the * represents spaces). Even though the name is spelt the same, the formula can't see it as the same. This seems like a small problem but I can't figure it out. Any help would be greatly appreciated.

If you would like to see the spreadsheet, let me know and i will email you one.

Thank you

 

[VoG]

If it is the value that you are looking up that contains redundant spaces try

=IF(ISNA(VLOOKUP(TRIM(B19),Statistics!$B$4:$I$616,8,FALSE)),0,(VLOOKUP(TRIM(B19),Statistics!$B$4:$I$616,8,FALSE)))

 

[haner74]

The redundant spaces are actually on the Statistics page. I need to be able to trim the spaces from the front and back part of the name there.

 

[VoG]

You could install ASAP http://www.asap-utilities.com/

Then select all of the data on the Statistics sheet then on the ASAP menu Text > Delete leading and trailing spaces.

 

[haner74]

I can try ASAP. But I found this interesting. I went to the Statistics page and tryed the trim function on the names there. It would only remove the spaces after the name, not before. Is there anything else within excel I could do to remove the spaces before the name?

 

[priddyboy]

There's a fairly straightforward explanation for why this won't work in the Excel help menu. You need to use a combination of TRIM and SUBSTITUTE to replace the nonbreaking space character with a space character. As a disclaimer, I haven't tried this since I don't have any of your data available. It seems pretty quick and to the point, though. Try something like this:

=IF(ISNA(VLOOKUP(TRIM(SUBSTITUTE(B19,CHAR(160),CHAR(32))),Statistics!$B$4:$I$616,8,FALSE)),0,(VLOOKUP(TRIM(B19),Statistics!$B$4:$I$616,8,FALSE)))

 

[haner74]

Thank you everyone for your help. I was able to get the asap add on working correctly to solve the problem. So far unable to get the latest suggestion to work properly but will continue to try it out. Great forum for help!