VLOOKUP values with an apostrophe (')

[Pauls123]

Hi all, I just noticed that when running a VLOOKUP formula as follows:

=IF(M29="x","scr",VLOOKUP(O29,$A$1:$B$9982,2,FALSE))

(the IF part of it has no relevance to my question). I have noticed if the range of the LOOKUP (which are all words), one or some of the words have an apostrophe (') after the word, my formula doesnt find it.

Eg, <TABLE style="WIDTH: 92pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=122><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><TBODY><TR style="HEIGHT: 22.5pt" height=30><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 92pt; HEIGHT: 22.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=30 width=122>Patrician's Glory</TD></TR></TBODY></TABLE>
Any thoughts on how to rectify this,.?

Regards, Paul

 

[Ron Coderre]

Can you give a more specific example of a value that exists in the lookup range, but cannot be found by the VLOOKUP?

I tried using your posted sample this way...
A1: Alpha
A2: Patrician's Glory
A3: Bravo

B1: 10
B2: 4
B3: 100

With D1: Patrician's Glory
This formula returns 4:
E1: =VLOOKUP(D1,A1:B3,2,FALSE)

 

[Pauls123]

Hi Ron,

and thanks. I just experimented with your example and it works. This is intriguing. There must be some reason why my words with 's dont work on my spreadsheet. Could be because they have been copied and pasted from a webpage. I shall continue to play with this.

Regards
Paul

 

[Pauls123]

me again, the plot thickens. When I retype the word "patrician's glory" into my column A where it originally was, it now works.

This is because I typed it in as opposed to the word being originally copied and pasted from a web page.

Obviously there is something about the ' that excel doesnt like. All other ordinary type words, it works fine.

Paul

 

[sulakvea]

Paul, did you copy / paste text from Word? or some other text file?

If so, the two apostrophes may be different:

Patricia’s Glory
Patricia's Glory

see how they are sort of angled differently? the first one was pasted from word, the second one was typed in excel

an easy fix would be to copy that symbol (the existing apostrophe) and find/replace it with the normal one (i.e. ctrl+H, find what: paste that symbol into the box - replace with: enter the apostrophe)

 

[Ron Coderre]

Try this...
A1: paste your text here
A2: type the text here

B1: a digit....start with 1
C1: =CODE(MID(A1,$B$1,1))
Copy that formula into D1

Now start comparing characters.
Increment the B1 value (1 then 2 then 3...etc) until the C1:D1 values do not match.
It could be that your copied "spaces" (char 32) are actually HTML non-breaking spaces (char 160).

Does that help?

 

[Pauls123]

Hi Ron and Sulakvea, Thanks very much for your replies. I've just tried them both. I got a bit lost with yours Ron. Then I tried Sulakvea's and even though my lookup range is currently around 2,000 names and growing, by highlighting Column A, and applying that control H, it worked, amazing.

I'll just have to keep applying it regularly in order to fix up recently added names.

It's funny, well frustrating is the better word how when your working with data originally from the net, all these little problems/hiccups keep jumping up at you,

Regards, Paul`

 

[sulakvea]

Paul, you can add a little worksheet change event to the sheet (this goes into the corresponding sheet module level)

Private Sub Worksheet_Change(ByVal Target As Range)
 Target.Replace "’", Replacement:="'", LookAt:=xlPart
End Sub

as soon as the other (weird) apostrophe is entered, or pasted into, the sheet - it will be automatically replaced with the one you want.

 

[Pauls123]

Hi there Sulakvea, this sounds very interesting, however all this type of programming is completely new for me. Can you explain in detail exactly where I put this.

For example my lookup range is in a worksheet named "Rating", and it occupies columns A and B.

Regards, Paul