Convert Number to Text for Index/Match
 

I have a column of data and it is a 4 character string, with all characters being numbers. The data ranges from 0004 to 8862. My data in the match column is a mix of text (the values with their leading zeros retained) and numbers - which is some of the rest of the numbers. The problem is that I cannot get all of the data in the column to be read as text; for example, 1000-2500 are numbers, but starting at 2502, they are text.

When doing an index/match, Excel treats 2210 the text value different from 2210 the number. How do I get them sync'd up so that my index/match works?

Convert Number to Text for Index/Match
 

hi,

i supposed that the data is placed in range A1:B5 and the value to
search in range F1
this is an array formula, you must validate with "ctrl + shift + enter"

=IF(ISERROR(MATCH(F1,$A$1:$A$5,0)),INDEX($B$1:$B$5 ,MATCH(F1,($A$1:$A$5)*1,0)),INDEX($B$1:$B$5,MATCH( F1,$A$1:$A$5,0)))

--
isabelle


Le 2012-10-31 15:52, a écrit :
I have a column of data and it is a 4 character string, with all characters being numbers. The data ranges from 0004 to 8862.
My data in the match column is a mix of text (the values with their leading zeros retained) and numbers - which is some of the rest of the numbers.
The problem is that I cannot get all of the data in the column to be read as text; for example, 1000-2500 are numbers, but starting at 2502, they are text.

When doing an index/match, Excel treats 2210 the text value different from 2210 the number. How do I get them sync'd up so that my index/match works?

Hi,

In your existing formula just put two dashes (--) before the lookup value. So if your formula is in cell B1 and the lookup value is in A1. Instead of doing match(A1,Data Range,0) do match(--(A1), Data Range,0).

This operator will convert any text (which looks like number) into a number.

Thanks,
Prashant