Number v TEXT confusion

Carty

Board Regular
Joined
Mar 3, 2009
Messages
76
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

 
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.
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,225,370
Messages
6,184,579
Members
453,244
Latest member
Todd Luet

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