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

 

[Carty]

"4th to 9th characters"
Incorrect MID(G4, 4, 5) gives the 4th to 8th characaters.

Agreed. My error - I added 5 to 4 and got 9

"I do not understand the +0 here"
Adding +0 to a string coerces the string into a number.
So "123" is a string
"123"+0 is 123 (a number).

Again my fault. This isnumber is checking the cell for a number. Why does the cell need to be coerced to be a number in this first instance? The second instance further along the equation makes sense

"12345"+0 = 0 ?
I dont see how that can occur.
The result should be 12345 (a number).

No, me either but that is how excel evaluated it. Interestingly if I open a new workbook I get 12345! Is it safe to presume the cell is formatted in a weird way on the original worksheet?

MID("AB 12345CDE", 4, 5)+0
results in 12345 (a number, I've just tested it).

Are you sure G4 contains "AB 12345CDE" ?
What's the length of G4, use =LEN(G4) in a blank cell.
The result should be 11. if it's anything else then G4 is NOT "AB 12345CDE"

It is both on the new worksheet and the original

Thank you for sticking with this. What you are writing is beginning to make sense but it appears the worksheet is not behaving in the way you or I expect.This can only be a problem my end. Just wish I knew how to resolve it

 

[Special-K99]

"Why does the cell need to be coerced to be a number in this first instance? The second instance further along the equation makes sense"

Numbers are not text. text are not numbers. They may LOOK like each other but they are stored differently.
Text is usually left-justified, numbers right-justfied.
If you VLOOKUP a value against a range, the VLOOKUP won't work if the lookup value is text ("12345") and the range contains a cell with numbers (12345).
So you have to make them the same.
If the range contains numbers and your string is text ("AB 12345CD") using MID(G4,4,5) will extract "12345" (still text). The VLOOKUP won't work.
By adding +0 it converts it to a number (12345). Now the VLOOKUP will work.
Another way would be to multiply by 1, result is now a number.

Could you post the worksheet or part of it?

You cant attach files on this forum.
Upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[Carty]

https://www.dropbox.com/s/5pecfgvy1y72y9k/ITM to Cofc auto fill project for MRexcel.docx?dl=0

This is a word document that has screen shots of the evaluation of the formula

https://www.dropbox.com/s/bl4a56y7255erxx/ITM to Cofc auto fill project for MRexcel.xlsx?dl=0
This is a sanitised version of the files I'm using. For ease I have made the network file a tab and adjusted the formulae to suit

 

[Aladin Akyurek]

https://www.dropbox.com/s/5pecfgvy1y72y9k/ITM to Cofc auto fill project for MRexcel.docx?dl=0

This is a word document that has screen shots of the evaluation of the formula

https://www.dropbox.com/s/bl4a56y7255erxx/ITM to Cofc auto fill project for MRexcel.xlsx?dl=0
This is a sanitised version of the files I'm using. For ease I have made the network file a tab and adjusted the formulae to suit

I think the CoC sheet is corrupt as it does recognize the underlying cell formats... Try to create a new sheet and then run the suggested formulas.

 

[Carty]

Aladin

Thanks for taking a look at the sheets.

As you suggested entering the formula on a new sheet worked perfectly so the fault must be in the sheet I was modifying. Would you mind explaining how you determined the issue was with the sheet?

Thanks

Paul

 

[Special-K99]

I think the CoC sheet is corrupt as it does recognize the underlying cell formats... Try to create a new sheet and then run the suggested formulas.

Yep, I agree.

If you perform the VLOOKUP on the worksheet (referring to Worksheet!$G$4 it returns 6789.
If you then copy that formula onto the CoC spreadsheet the value changes (!!) you get the text in G1 which is incorrect.

It's not cos G4 is a merged cell is it?

I've unmerged it but it hasn't made any difference.

 

[Aladin Akyurek]

Aladin

Thanks for taking a look at the sheets.

As you suggested entering the formula on a new sheet worked perfectly so the fault must be in the sheet I was modifying. Would you mind explaining how you determined the issue was with the sheet?

Thanks

Paul

Since the suggestion using +0 coercer is simply correct, I checked whether the following worked in a cell of that CoC sheet:

="12345"+0

It returned 0 instead of 12345, a behavior 100% indicative of an incorrect/faulty state.

 

[Carty]

Yep, I agree.

If you perform the VLOOKUP on the worksheet (referring to Worksheet!$G$4 it returns 6789.
If you then copy that formula onto the CoC spreadsheet the value changes (!!) you get the text in G1 which is incorrect.

It's not cos G4 is a merged cell is it?

I've unmerged it but it hasn't made any difference.

G4 and H4 are merged

 

[Carty]

Since the suggestion using +0 coercer is simply correct, I checked whether the following worked in a cell of that CoC sheet:

="12345"+0

It returned 0 instead of 12345, a behavior 100% indicative of an incorrect/faulty state.

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

 

[Carty]

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