Excel bug? lookup functions return #n/a

[unigee]

Hi everyone,

I have a column with a list of part numbers. The format property of this column is set to TEXT (as the part numbers contain both numbers and characters)

I am performing a MATCH (and also some other lookup functions such as VLOOKUP) calculation to find the row number of a part number.

=MATCH("PART001", B:B, 0)

PART001 exists in B:B but the return is always #N/A

Now if I click in the cell that contains the word PART001, then click the formula bar, then just press ENTER (without changing anything) - The match function returns the correct row number.

Having to click on a cell, press enter is not really viable as I have several thousand rows to do this on.

Is this is a bug?

Thanks

 

[Jonmo1]

Is the column of part numbers imported or copy/pasted from some other application?

On the cell that contains PART001 (say it's B12 for example), what do these formulas return
=LEN(B12)
=CODE(RIGHT(B12,1))

 

[unigee]

The actual value is "14696034" not PART001 ( I was just trying to be generic, but PART001 returned the correct result )

The data is copied from a .csv file (that I created by data mining a sage database)

So to your questions
=LEN(B7) returns 8
=CODE(RIGHT(B7,LEN(B7))) return 49

Something I have noticed too is, when I click in the formula bar and press ENTER, I see a small triangle at the corner of the cell and clicking on the popup tells me Number Stored As Text

However, the troublesome cells don't have this triangle icon.

I seem to only have an issue when the part numbers are only digits with no alpha characters

Thanks

 

[Jonmo1]

OK, the problem is Numbers Stored As Text..

Try Highlighting the Column
click Data - Text to Columns
Select Deliminated
Click finish.

 

[unigee]

Fantastic!

I had to click next twice, and then set the Column Data Format to Text in the Convert Text to Column wizard.

Thank you so much, this has been fustrating me for the past couple of hours trying to work it out.

Just a side question, what was your logic behind
=CODE(RIGHT(B12,1)) ?

was you looking for an hidden character at the end of the string?

Anyway, thanks for the help.

Can actually get back to some work.

 

[Jonmo1]

Fantastic!

I had to click next twice, and then set the Column Data Format to Text in the Convert Text to Column wizard.

Thank you so much, this has been fustrating me for the past couple of hours trying to work it out.

Just a side question, what was your logic behind
=CODE(RIGHT(B12,1)) ?

was you looking for an hidden character at the end of the string?

Anyway, thanks for the help.

Can actually get back to some work.

That was exactly my thinking, because you originally said the value was
PART001
That through me for a loop because it couldn't be "numbers stored as text" if that was the actual value...