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:

[TABLE="width: 315"]
<tbody>[TR]
[TD]input[/TD]
[TD]output[/TD]
[TD]notes[/TD]
[/TR]
[TR]
[TD]296.41516.asm[/TD]
[TD]O:\[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]524.00240.par[/TD]
[TD]I:\[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]525.02800.par[/TD]
[TD]I:\[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]524.00141.par[/TD]
[TD]I:\[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]496.42928.asm[/TD]
[TD]O:\[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]496.42928-1.par[/TD]
[TD]O:\[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]496.42928-2.par[/TD]
[TD]O:\[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]525.01260.par[/TD]
[TD]I:\[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]523.01340.par[/TD]
[TD]I:\[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]523.06370.par[/TD]
[TD]I:\[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]596.44990.par[/TD]
[TD]O:\[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]540.25471.par[/TD]
[TD]T:\[/TD]
[TD]this should be i:\[/TD]
[/TR]
[TR]
[TD]496.42935.asm[/TD]
[TD]O:\[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]496.42935-1.par[/TD]
[TD]O:\[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]496.42935-2.par[/TD]
[TD]O:\[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]523.10590.par[/TD]
[TD]T:\[/TD]
[TD]this should be i:\[/TD]
[/TR]
[TR]
[TD]525.02790.par[/TD]
[TD]T:\[/TD]
[TD]this should be i:\[/TD]
[/TR]
[TR]
[TD]598.96123.par[/TD]
[TD]O:\[/TD]
[TD]this should be q:\[/TD]
[/TR]
</tbody>[/TABLE]

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