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

turtlepokerman

New Member
Joined
Jun 18, 2012
Messages
28
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:
Text Import DataMy StringsFormulaResult
15EZ-73A15FX-72A=MATCH(B2,$A$2:$A$3,0)#N/A
15FX-72A15EZ-73A=MATCH(B3,$A$2:$A$3,0)#N/A

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 246px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
=MATCH(B2,$A$2:$A$3,0)

<tbody>
</tbody>
</body>Any help is appreciated as I have over 1000 rows of data.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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
Back
Top