Input and output in Excel

[dharvey]

Hi all,

i am have a small issue with my excel function. my goal is if i enter a file name it outputs the drive letter is should be on. the problem is that all of the file names are numbers and i only want it to search the first three characters but it does not. my function is:

=LEFT(IF(ISNUMBER(SEARCH("96",C27)),"O",IF(ISNUMBER(SEARCH("98",C27)),"Q",IF(ISNUMBER(SEARCH("99",C27)),"K",IF(ISNUMBER(SEARCH("90",C27)),"T",IF(ISNUMBER(SEARCH("91",C27)),"T",IF(ISNUMBER(SEARCH("71",C27)),"T",IF(ISNUMBER(SEARCH("68",C27)),"T","I"))))))),3)

what come out with is as follows:

input	output	notes
296.41516.asm	O:\
524.00240.par	I:\
525.02800.par	I:\
524.00141.par	I:\
496.42928.asm	O:\
496.42928-1.par	O:\
496.42928-2.par	O:\
525.01260.par	I:\
523.01340.par	I:\
523.06370.par	I:\
596.44990.par	O:\
540.25471.par	T:\	this should be i:\
496.42935.asm	O:\
496.42935-1.par	O:\
496.42935-2.par	O:\
523.10590.par	T:\	this should be i:\
525.02790.par	T:\	this should be i:\
598.96123.par	O:\	this should be q:\

<tbody>
</tbody>

for some reason it is going past the first three character and reading something it should not (in red).

thanks

 

[Fluff]

You are looking at the entire text, not just the first 3 numbers.
One option is to change your formula like
=IF(ISNUMBER(SEARCH("96",LEFT(C27,3))),"O",

 

[Fluff]

Slightly shorter
=IF(OR(ISNUMBER(SEARCH({"68","71","91","90"},LEFT(C27,3)))),"T",IF(ISNUMBER(SEARCH("96",LEFT(C27,3))),"O",IF(ISNUMBER(SEARCH("98",LEFT(C27,3))),"Q",IF(ISNUMBER(SEARCH("99",LEFT(C27,3))),"K","I"))))

 

[dharvey]

thanks so much, that did the trick. i did not realize that you would have to do that with in each search function.

 

[Fluff]

You're welcome & thanks for the feedback