Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Check out our Excel Resources

Re: Left Function

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


does it have to use the left function? could you use...

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.


Re: Left Function

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


THANK YOU

Posted by Elizabeth on February 01, 2002 6:32 AM
Big thanks to all for your input. With the suggestions I was able to accomplish what I needed to.. :)


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.