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

 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
When substituting a string add zero, to turn it into a number, ie

=INDEX('[workbook]worksheet'!$H$1:$H$65536,MATCH(MID($G$4,4,5)+0,'[workbook]worksheet'!$O$1:$O$65536,))

or this should cater for either text or number in G4

=INDEX('[workbook]worksheet'!$H$1:$H$65536,MATCH(MID(IF(ISTEXT($G$4),MID($G$4,4,5)+0,G4),'[workbook]worksheet'!$O$1:$O$65536,))
 
Last edited:
Upvote 0
Hi Special-K99

I tried both versions. The first gave the data in cell A25 of the same sheet that G4 is on, the second gave me a #value ! error. When I evaluated the second term I got a ref error after it has resolved the second mid term. It looks like this;

=Index('[workbook]worksheet!'$H$1:$H$65536,MATCH(MID(0,'[Workbook]worksheet!'$O$1:$O$65336,),)) which then goes to =Index('[workbook]worksheet!'$H$1:$H$65536,MATCH(MID(0,#ref !),)) which leads to =Index('[workbook]worksheet!'$H$1:$H$65536,MATCH(#value !,)) etc

Thank you
 
Last edited:
Upvote 0
Why have you got MID(0, ???

MID(0 doesnt even appear in your original formula (nor either of mine) ?

The G4 in my formula should probably be $G$4
 
Upvote 0
If
MID($G$4,4,5) delivers a text number, the suggestion should work:

=INDEX([workbook]worksheet!$H$1:$H$65536,MATCH(IF(ISNUMBER($G$4+0),$G$4,MID($G$4,4,5))+0,[workbook]worksheet!$O$1:$O$65536,0))
 
Upvote 0
Why have you got MID(0, ???

MID(0 doesnt even appear in your original formula (nor either of mine) ?

The G4 in my formula should probably be $G$4

I clicked on the cell with the formula in then used "evaluate formula" in the "formulas" tab to see where the formula crashed. It resolved the Mid term down to 0, not 12345 as I was expecting
 
Upvote 0
If
MID($G$4,4,5) delivers a text number, the suggestion should work:

=INDEX([workbook]worksheet!$H$1:$H$65536,MATCH(IF(ISNUMBER($G$4+0),$G$4,MID($G$4,4,5))+0,[workbook]worksheet!$O$1:$O$65536,0))
Hi Aladin. This gives me the title of column H. May I try to break down your formula as I understand it?
If there is a number in G4 {(IF(ISNUMBER($G$4+0)} [p.s. I do not understand the +0 here] use the number {,$G$4,} else use the 4th to 9th characters {MID($G$4,4,5))}and turn it into a number {+0,}
 
Upvote 0
If I isolate the IF term with G4 set to AB 12345CDE and then evaluate the cell it recognises that AB 12345CDE is not a number. It then strips out 12345 but displays it as "12345"+0. Evaluating this gives 0!
 
Upvote 0
"4th to 9th characters"
Incorrect MID(G4, 4, 5) gives the 4th to 8th characaters.

"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).

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

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"
 
Last edited:
Upvote 0
Hi Aladin. This gives me the title of column H. May I try to break down your formula as I understand it?
If there is a number in G4 {(IF(ISNUMBER($G$4+0)} [p.s. I do not understand the +0 here] use the number {,$G$4,} else use the 4th to 9th characters {MID($G$4,4,5))}and turn it into a number {+0,}

If

=INDEX([workbook]worksheet!$H$1:$H$65536,MATCH(G4,[workbook]worksheet!$O$1:$O$65536,0))

works when G4 is a number (I suppose a whole number),

=INDEX([workbook]worksheet!$H$1:$H$65536,MATCH(IF(ISNUMBER($G$4+0),$G$4,MID($G$4,4,5))+0,[workbook]worksheet!$O$1:$O$65536,0))

will also work when G4 is a number or text containing a number.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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