MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to make a macro check for the first and third characters in a string


Posted by Kurt on September 02, 2000 6:16 PM

I HAVE A QUESTION IS THERE AWAY TO HAVE THIS CHECK FOR A MATCH OF CERTAIN LETTERS IN A WORD INSTEAD OF THE WHOLE WORD. EXAMPLE:COLUMN A: BAT,DOG
COLUMN B:BAG,FAT,CAT,CAP,DIG RESULT WOULD BE COLUMN C:BAG IF TESTING FOR FIRST TWO LETTERS OR FAT,CAT IF TESTING FOR LAST TWO LETTERS OR DIG IF TESTING FOR FIRST AND THIRD LETTERS.!!!
THANKS

Kurt


Posted by Celia on September 03, 0100 6:35 AM


Kurt
Try this non-macro way of doing it.
Using your example, put the following array formulas(Ctrl+Shift+Enter) in cells C1,D1 and E1 and fill them down to row 5 :-

=IF(ISNA(MATCH(LEFT(B1,2)&"?",$A$1:$A$5,0)=1),"",B1)

=IF(ISNA(MATCH("?"&RIGHT(B1,2),$A$1:$A$5,0)>0),"",B1)

=IF(ISNA(MATCH(LEFT(B1,1)&"?"&RIGHT(B1,1),$A$1:$A$5,0)>0),"",B1)

Celia


Posted by Thanks!! on September 03, 0100 10:34 AM

Thanks Celia,

How long have you been doing this?

Have a great Labor Day Weekend!!


Kurt