Arul.rajesh
Active Member
- Joined
- Sep 20, 2011
- Messages
- 285
Sorry if the title misguided you.
From a list of values in column "A" need to identify the values which begin with a certain string of characters listed here
CK
Credit Card
FRM
Xfer
Visa/Master
Transf
Trnsf
The data in column A looks like
AMERIHEALTH MANUAL P
AMERIHEALTH PAYMENT
CK 105
CK 1466
CK 2834
CK 323
CK 3255
CK 703
CK 7171
CK 828
CR CARD PAYMENT AMER
DELTA DENTAL
DUPLICATE POSTING
FRM ACCT 13470724
FRM ACCT 13485586
GHI PAYMENT
HEALTH PARTNERS
INCIDENTAL ONLY CHGS
MCAID GOVT PAYMENT M
ST CLOUD HLTHCARE CT
ST MARYS LIFE CENTER
TRANSFER FROM 259826
VISA/MASTER CHARGE
XFER FROM 260482430
XFER TO 8012197599
The ones beginning with the any of the strings listed above must be identified by a formula in column B.
This array formula works but is there a more elegant solution?
{=IF(SUM(N(IFERROR(SEARCH($E$1:$E$7,A1),0)))=1,"exclude","include")}
Assuming the first list of values is in $E$1:$E$7.
From a list of values in column "A" need to identify the values which begin with a certain string of characters listed here
CK
Credit Card
FRM
Xfer
Visa/Master
Transf
Trnsf
The data in column A looks like
AMERIHEALTH MANUAL P
AMERIHEALTH PAYMENT
CK 105
CK 1466
CK 2834
CK 323
CK 3255
CK 703
CK 7171
CK 828
CR CARD PAYMENT AMER
DELTA DENTAL
DUPLICATE POSTING
FRM ACCT 13470724
FRM ACCT 13485586
GHI PAYMENT
HEALTH PARTNERS
INCIDENTAL ONLY CHGS
MCAID GOVT PAYMENT M
ST CLOUD HLTHCARE CT
ST MARYS LIFE CENTER
TRANSFER FROM 259826
VISA/MASTER CHARGE
XFER FROM 260482430
XFER TO 8012197599
The ones beginning with the any of the strings listed above must be identified by a formula in column B.
This array formula works but is there a more elegant solution?
{=IF(SUM(N(IFERROR(SEARCH($E$1:$E$7,A1),0)))=1,"exclude","include")}
Assuming the first list of values is in $E$1:$E$7.