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

haner74

New Member
Joined
Oct 17, 2002
Messages
4
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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

New Member
Joined
Oct 17, 2002
Messages
4
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.
 

haner74

New Member
Joined
Oct 17, 2002
Messages
4

ADVERTISEMENT

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

Board Regular
Joined
Feb 10, 2006
Messages
60
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

New Member
Joined
Oct 17, 2002
Messages
4
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!
 

Forum statistics

Threads
1,136,649
Messages
5,676,990
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top