Match Function Giving #N/A Error After Text Import Wizard on Known Matches

[turtlepokerman]

Greetings World,

I am using Excel 2010.

I am trying to use a Match function to find exact matches to strings in a pdf file that I extracted using text import wizard. The only way that I can get the formula to give me the correct result is to manually retype the Match function's lookup value's reference cell.

I have tried the following solutions to fix the problem without completely retyping the cell:
1. Recalculate the formula Result
2. Change formatting of cell from General -> Text -> General
3. Hit Enter on the reference cell and formula cell
4. Turning on/off Automatic Calculation Results

Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Text Import Data[/TD]
[TD]My Strings[/TD]
[TD]Formula[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]15EZ-73A[/TD]
[TD]15FX-72A[/TD]
[TD]=MATCH(B2,$A$2:$A$3,0)[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]15FX-72A[/TD]
[TD]15EZ-73A[/TD]
[TD]=MATCH(B3,$A$2:$A$3,0)[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 246px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]=MATCH(B2,$A$2:$A$3,0)[/TD]
[/TR]
</tbody>[/TABLE]
</body>Any help is appreciated as I have over 1000 rows of data.

 

[turtlepokerman]

As a followup example I copy & pasted the strings into a word document and back into excel with no success of matching between the two strings. I wish this forum had attachments...

Thanks,
turtlepokerman

 

[AhoyNC]

Check to see if there are any trailing or leading spaces in the data you are importing.
In your example the text string should be 8 characters. You can use the LEN function to check LEN(A2) should return 8.

 

[63falcondude]

I don't think that this would be the problem but it's worth a shot... What happens if you double click on B2 and then hit enter? If this makes the formula for row 2 work, select all of column B (by clicking on the letter B), Data, Text to Columns, Finish.

 

[turtlepokerman]

You all were incorrect. Here is the solution:

Apparently there are hidden characters and a null character that appear when you import text from a pdf. Steps to resolve this problem for me:

Step 1: Use the below formula on the corrupted text (where H6 is the corrupted text)
=CLEAN(SUBSTITUTE(SUBSTITUTE(H6,CHAR(13),""),CHAR(10),""))

Step 2: I had to use Find/Replace All to Find "-" and Replace with "-". This fixed the hidden character that excel cannot fix with a formula.

Thanks,
turtlepokerman

 

[63falcondude]

You all were incorrect. Here is the solution:

Glad you figured it out.