data from internet not showing up

[AJSloss]

I copied and pasted a table from the internet that has "*Bobby Abreu" in cell
B3. In cell AF3 I have the formula: =VLOOKUP(IF(LEFT(B3, 1)="*", MID(B3, 2,
100), B3), draft, 2, FALSE), resulting in an error (#N/A).

If I manually type "*Bobby Abreu" in cell B3, then the correct result, "65",
turns up in cell AF3.

I've gone through evaluate formula and both times it shows that vlookup is
searching for "Bobby Abreu" in the draft table, however, when I have the
internet data in, it keeps showing the error.

I don't want to have to manually type in the names, as there are about 600.
Is there something with the format of the cell that is causing this, or some
kind of hidden characters in the internet data?

 

[Dave Peterson]

If you put this in a cell:

=IF(LEFT(B3, 1)="*", MID(B3, 2, 100), B3)
What gets returned?

How about
=len(b3)=len("*bobby abreu")
Does this return true or false?

I'm guessing that you have other characters in that cell. Depending on what
they are, you may be able to adjust the formula or fix the data.

Chip Pearson has a very nice addin that will help determine what's in the cell:
http://www.cpearson.com/excel/CellView.aspx

 

[AJSloss]

The answer to the first question is "bobby abreu"

The answer to the second question is that it returns true.

However, when I compare the internet data cell (that contains "*Bobby
Abreu") to a manually typed cell (containing "*Bobby Abreu") using exact, it
returns false.

 

[Dave Peterson]

Maybe it's the space character between first and last names.

What did Chip's addin show as that character?

Was it x160 (the HTML non-breaking space character)?
Wis it x32 (a normal space character)?

 

[AJSloss]

Thanks for hanging with me, I was unable to download that add-in on my work
computer. I just ran it and the space has a 160 in Dec line and there is a
"^" underneath it in the Special line.

 

[Dave Peterson]

That means that it's not a "normal" space character--it's a non-breaking space
character to keep both words together on the same line (no wrapping at that
break between words).

You can either clean up the character (along with the asterisk???) using a
macro. This would make the formula easier since you wouldn't have to include
those checks.

Or you could even use a helper column that cleans that data first, then your
=vlookup() formula would be a little easier:

=substitute(IF(LEFT(B3, 1)="*",MID(B3, 2,100),B3),char(160)," ")

Or you could embed that into your existing =vlookup() formula:
=vlookup(substitute(.....),draft,2,FALSE)

Personally, I like cleaning up the data with a macro. That makes the formula
easier and it means that I don't have to remember to make any other formulas
more complex.

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

'yes, there's a tilde in front of the asterisk. That tells excel not
'to treat the asterisk as a wild card.
myBadChars = Array("~*", Chr(160))

'remove the asterisk by changing it to ""
'and change the funny character to a space.
myGoodChars = Array("", " ")

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
'I just changed column A. Adjust the range to what you need.
ActiveSheet.Range("A:A").Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Dave Peterson]

ps. If you clean up your data with the macro (or with the helper cell), the
=vlookup() formula is much more basic:

=vlookup(b3,draft,2,false)
(or if the corrected value is now in column C)
=vlookup(c3,draft,2,false)