Number v TEXT confusion

[Carty]

Hello

I have a formula that looks at the contents of cell G4 anduses the data to reference a specified column in another workbook:
=INDEX('[workbook]worksheet'!$H$1:$H$65536,MATCH($G$4,'[workbook]worksheet'!$O$1:$O$65536,))
This works fine as long as the data in G4 is a number. Ifthe cell contains a string (e.g. AB 12345CDE) the result is #N/A.

I can isolate the number by using MID:

=MID($G$4,4,5) which returns 12345 but if I substitute the MID term in the MATCHterm:

=INDEX('[workbook]worksheet'!$H$1:$H$65536,MATCH(MID($G$4,4,5),'[workbook]worksheet'!$O$1:$O$65536,))I get the same #N/A error.
Have I unwitting created a text term that cannot be used tosearch for a number or is the methodology wrong?
Thank you

Paul

 

[Aladin Akyurek]

When I enter ="12345"+0 in a cell on the CoC tab I get blank cell! On a new tab I get 12345

Do you have any idea how to correct this behaviour? Is it a formatting thing?

Thanks

Paul

Gentlemen

Would the fact that the workbook is of an old excel version (97 to 2003 file type) have any bearing on this, especially as I am using excel 2013 to view/modify it?

Paul

That's why I suggested to replace CoC as it is in a faulty state.

 

[Carty]

Thank you both for your help on this

Paul