MrExcel Consulting
Your One Stop for Excel Tips & Solutions

Left Function


Posted by Elizabeth on January 31, 2002 6:03 PM

I have some codes all in one cell example....LMDF
I am trying to use the left funtion to read first the L and if the first one is not an L then have it look at the second characture to see if it is an so on.
I can get it to find it in the first spot but can't get it to go to the next character to look for the L..
Also, I would like if it does find it to say "Yes" if not then 0.

Any suggestions?? Any and all help is greatly appreciated.. Thanks for your time...

Posted by Dan Aragon on January 31, 2002 6:23 PM

I think your best bet is to use a VBA macro to do this because your formula is going to get kind of long, depending on how many characters you need to search. But, a spreadsheet formula might look something like:

=IF(OR(Left(A1,1)="L",Mid(A1,2,1)="L",...),"Yes",0)

I think you can see how long this could get. This tests the first two characters. The "..." in the formula is for you to fill in for however many other characters you want to test for.

HTH

Posted by anno on January 31, 2002 6:58 PM

=IF(ISERROR(SEARCH("L",A1)),0,"Yes")? you can also use FIND instead of SEARCH if you want it to be case sensitive.

Posted by Ivan F Moala on January 31, 2002 11:05 PM

Elizabeth
IF you are searching for the text string LMDF within
a string then use Anno's formula but change to;
=IF(ISERROR(FIND("LMDF",T(D8))),0,"Yes") where
D8 is the cell searched.....also note Anno's
comments on cases.


Ivan

Posted by Elizabeth on February 01, 2002 6:32 AM

THANK YOU

Big thanks to all for your input. With the suggestions I was able to accomplish what I needed to.. :)