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

#### haner74

##### New Member
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### VoG

##### Legend
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
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

##### Legend
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

##### New Member
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
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
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!

Replies
0
Views
188
Replies
11
Views
424
Replies
3
Views
437
Replies
0
Views
196
Replies
7
Views
175

1,172,016
Messages
5,878,752
Members
433,369
Latest member
Applewood

### 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?

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