A function similar to vlookup, but matching on partial values.

[SerenityNetworks]

I am trying to assign categories to our monthly financial transactions. I download the transactions from our banks and credit cards, then import them into a workbook. I'll see transactions like the following:

Description EMPIRE DISTRICT ELEC BILL 220906 35849771 LastName EMPIRE DISTRICT ELEC BILL 221005 35849771 LastName EMPIRE DISTRICT ELEC BILL 221107 35849771 LastName EMPIRE DISTRICT ELEC BILL 221205 35849771 LastName TANEY COUNTY REG Utilities, Sewer 220118 703568 LASTN TANEY COUNTY REG Utilities, Sewer 220217 703568 LASTN TANEY COUNTY REG Utilities, Sewer 220317 703568 LASTN VENMO PAYMENT 220912 1022310974417 ANDREW LASTN VENMO PAYMENT 220926 1022557429615 ANDREW LASTN VENMO PAYMENT 220928 1022604113014 ANDREW LASTN SSA TREAS 310 XXSOC SEC 011222 XXXXX0381A SSA LASTNM SSA TREAS 310 XXSOC SEC 011222 XXXXX4328A SSA LASTNF SSA TREAS 310 XXSOC SEC 020922 XXXXX0381A SSA LASTNM SSA TREAS 310 XXSOC SEC 020922 XXXXX4328A SSA LASTNF BROKERAGE 220301 6369V49702722 LASTN BROKERAGE 220330 6369V49702722 LASTN BROKERAGE 220202 6369V49702722 LASTN 220205 Transfer to Savings 220305 Transfer to Savings

You'll note that there are base similarities for each transaction. Sometimes the date creates the difference and sometimes it is a date and/or transaction number. I'd like to trim it down to base descriptions, like the following, and have them in a separate worksheet with Group and Detail values.

Description	Group	Detail
EMPIRE DISTRICT ELEC BILL	Purchase	Utilities, Electric
TANEY COUNTY REG Utilities, Sewer	Purchase	Utilities, Sewer
VENMO PAYMENT	Purchase	VARIES
SSA TREAS 310 XXSOC SEC	Income	Income, Soc Sec
BROKERAGE	Income	Income, Disbursement
Transfer to Savings	Transfer	Transfer

Then the next time I import a bunch of transactions import a bunch of transactions into the workbook, for each transaction (as in the first table) I'd like to be able to return the Group and Detail values. The vlookup, match, lookup, and similar functions don't seem to work, as the length of what I'm looking up varies and doesn't always start at the left.

I'd love some input on how I might accomplish my goal. I'm having a brain cramp on a solution.

Thanks in advance,
Andrew

 

[etaf]

what I'm looking up varies and doesn't always start at the left.

all your examples start on the left, except the savings which seems to be the right - so using an IF and left/right - could probably do that easier

find the 1st 6 numbers in a string and provide the LEFT part - UNLESS the string starts with a 6 digit number and then provide the right part

What version of excel do you have - because in the later version 365 , we have a lot of additional text functions - like textsplit , textbefore, textafter
which may make this easier to do

can you give more examples of the text - because at the moment it looks like , if there is a date (number ) for the 1st 6 digits - then use RIGHT()
otherwise find the first 6 digit number n the text and then use LEFT()
so a textbefore() and textafter() would work well

OR LEFT / RIGHT with an iferror
if the example above is the only variables

=IFERROR(LEFT(A1,FIND("------",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0","-"),"1","-"),"2","-"),"3","-"),"4","-"),"5","-"),"6","-"),"7","-"),"8","-"),"9","-"))-2),RIGHT(A1,LEN(A1)-7))

Book1
	A	B		D
1	EMPIRE DISTRICT ELEC BILL 220906 35849771 LastName			EMPIRE DISTRICT ELEC BILL
2	EMPIRE DISTRICT ELEC BILL 221005 35849771 LastName			EMPIRE DISTRICT ELEC BILL
3	EMPIRE DISTRICT ELEC BILL 221107 35849771 LastName			EMPIRE DISTRICT ELEC BILL
4	EMPIRE DISTRICT ELEC BILL 221205 35849771 LastName			EMPIRE DISTRICT ELEC BILL
5	TANEY COUNTY REG Utilities, Sewer 220118 703568 LASTN			TANEY COUNTY REG Utilities, Sewer
6	TANEY COUNTY REG Utilities, Sewer 220217 703568 LASTN			TANEY COUNTY REG Utilities, Sewer
7	TANEY COUNTY REG Utilities, Sewer 220317 703568 LASTN			TANEY COUNTY REG Utilities, Sewer
8	VENMO PAYMENT 220912 1022310974417 ANDREW LASTN			VENMO PAYMENT
9	VENMO PAYMENT 220926 1022557429615 ANDREW LASTN			VENMO PAYMENT
10	VENMO PAYMENT 220928 1022604113014 ANDREW LASTN			VENMO PAYMENT
11	SSA TREAS 310 XXSOC SEC 011222 XXXXX0381A SSA LASTNM			SSA TREAS 310 XXSOC SEC
12	SSA TREAS 310 XXSOC SEC 011222 XXXXX4328A SSA LASTNF			SSA TREAS 310 XXSOC SEC
13	SSA TREAS 310 XXSOC SEC 020922 XXXXX0381A SSA LASTNM			SSA TREAS 310 XXSOC SEC
14	SSA TREAS 310 XXSOC SEC 020922 XXXXX4328A SSA LASTNF			SSA TREAS 310 XXSOC SEC
15	BROKERAGE 220301 6369V49702722 LASTN			BROKERAGE
16	BROKERAGE 220330 6369V49702722 LASTN			BROKERAGE
17	BROKERAGE 220202 6369V49702722 LASTN			BROKERAGE
18	220205 Transfer to Savings			Transfer to Savings
Sheet1

Cell Formulas
Range	Formula
D1:D18	D1	=IFERROR(LEFT(A1,FIND("------",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0","-"),"1","-"),"2","-"),"3","-"),"4","-"),"5","-"),"6","-"),"7","-"),"8","-"),"9","-"))-2),RIGHT(A1,LEN(A1)-7))

 

[SerenityNetworks]

Brilliant! Thank you. I hadn't thought to use the substitute function to assist with the trimming. But I see my examples left a lot to be desired. Your trimming captured about a third of my actual transactions, which is still a significant help. Below are some examples of what was missed and what I'd prefer my trimmed values to be. If possible, I'd prefer it not to be case sensitive. That said, I'm sure open to keeping within some rules on my manually trimmed values (like putting numbers and/or special characters within quotes, or not allowing numbers at all), and I'm sure open to just letting the formula throw a "CHECK ME" value when it runs across something that doesn't fit the convention.

I am using Microsoft Excel for Microsoft 365 MSO, so functions like textsplit and textbefore are supported. (I used to be adept at Excel, but it looks like a lot has come along since my retirement and ceasing to keep current.)

Thanks again for the help. This task is way beyond my current ability.

Downloaded Transaction Description	etaf's trimming	trimmed preference
18 INDUSTRIAL PARK DHOLLISTER, MO	STRIAL PARK DHOLLISTER, MO	18 INDUSTRIAL PARK DHOLLISTER, MO
Amazon.com*5V4V696K3	com*5V4V696K3	Amazon.com
Amazon.com*7Q15C5DJ3	com*7Q15C5DJ3	Amazon.com
AMZN Mktp US*VG8IP9PY3	tp US*VG8IP9PY3	AMZN Mktp US
ARBY'S 566	566	ARBY
AT&T *PAYMENT	T *PAYMENT	AT&T *PAYMENT
ATT*BILL PAYMENT	L PAYMENT	ATT*BILL PAYMENT
AUTOMATIC PAYMENT - THANK	IC PAYMENT - THANK	AUTOMATIC PAYMENT - THANK
BATTLEFIELD MALL SPRINGFIELD MO (P9119002400YY5D5E)	BATTLEFIELD MALL SPRINGFIELD MO (	BATTLEFIELD MALL
BEACH 5263	263	BEACH
BIG D'S BBQ BRANSON Branson, MO	BBQ BRANSON Branson, MO	BIG D'S BBQ
BRANSON MEADOWS CINEMA	MEADOWS CINEMA	BRANSON MEADOWS CINEMA
CBI*ASHAMPOO	AMPOO	CBI*ASHAMPOO
CBN DONATION	ATION	CBN
PURCHASE AUTHORIZED ON 02/02 CBN DONATION 757-226-3334 VA S463033401913091 CARD 2004	PURCHASE AUTHORIZED ON 02/02 CBN DONATION	CBN
CHECK # 9061 (BCCA)	9061 (BCCA)	CHECK
CHECK # 9063 (Stormy Point Village)	9063 (Stormy Point Village)	CHECK
COSTCO WHSE #1486	WHSE #1486	COSTCO WHSE
COUNTRY MART #70	MART #70	COUNTRY MART
COUNTRY MART HOLLISTER, MO	MART HOLLISTER, MO	COUNTRY MART
******* BARREL #274 PEARL	BARREL #274 PEARL	******* BARREL
******* BARREL #557 SPANI	BARREL #557 SPANI	******* BARREL
FIDELITY LIFE ASSOCIATION	Y LIFE ASSOCIATION	FIDELITY LIFE ASSOCIATION
GOODWILL OZARK	L OZARK	GOODWILL
INTEREST PAYMENT	T PAYMENT	Interest Payment
Interest Payment	t Payment	Interest Payment
MENARDS HOLLISTER MO	HOLLISTER MO	MENARDS
MENARDS HOLLISTER MOHOLLISTER, MO	HOLLISTER MOHOLLISTER, MO	MENARDS
MOBILE DEPOSIT : REF NUMBER :315110469806	MOBILE DEPOSIT : REF NUMBER	MOBILE DEPOSIT
MONTHLY MAINTENANCE FEE	MAINTENANCE FEE	MONTHLY MAINTENANCE FEE
MONTHLY MAINTENANCE FEE WAIVED	MAINTENANCE FEE WAIVED	MONTHLY MAINTENANCE FEE
NEW YORK LIFE INS. PREM. FEB 23 C4740002 NAME	NEW YORK LIFE INS. PREM. FEB 23	NEW YORK LIFE INS. PREM.
NEW YORK LIFE INS. PREM. JAN 23 C4740002 NAME	NEW YORK LIFE INS. PREM. JAN 23	NEW YORK LIFE INS. PREM.
ONLINE PAYMENT - THANK YOU	PAYMENT - THANK YOU	ONLINE PAYMENT
ONLINE PAYMENT THANK YOU	PAYMENT THANK YOU	ONLINE PAYMENT
ONLINE PAYMENT, THANK YOU	PAYMENT, THANK YOU	ONLINE PAYMENT
Online Payment, Thank you	Payment, Thank you	ONLINE PAYMENT
ONLINE TRANSFER FROM LINDSAY A SAVINGS XXXXXXXXX2783 REF #IB0HBJC6K2 ON 12/31/22	TRANSFER FROM LINDSAY A SAVINGS XXXXXXXXX2783 REF #IB0HBJC6K2 ON 12/31/22	ONLINE TRANSFER
ONLINE TRANSFER REF #IB0HD4THK4 TO PERSONAL LINE OF CREDIT XXXXXX8909 ON 01/06/23	TRANSFER REF #IB0HD4THK4 TO PERSONAL LINE OF CREDIT XXXXXX8909 ON 01/06/23	ONLINE TRANSFER
OVERDRAFT XFER FROM CREDIT CARD OR LINE	FT XFER FROM CREDIT CARD OR LINE	OVERDRAFT XFER FROM CREDIT CARD OR LINE
PETCO 1669 63516694	PETCO 1669	PETCO
PILOT 00015503	PILOT	PILOT
Prime Video *1V29C7FW3	ideo *1V29C7FW3	Prime Video
Prime Video *HP3038EO1	ideo *HP3038EO1	Prime Video
RECURRING TRANSFER FROM NAME CHECKING REF #OP0HQZWSQQ XXXXXX4321	NG TRANSFER FROM NAME CHECKING REF #OP0HQZWSQQ XXXXXX4321	RECURRING TRANSFER FROM
RECURRING TRANSFER FROM NAMECHECKING REF #OP0HVJ5BD8 XXXXXX4321	NG TRANSFER FROM NAMECHECKING REF #OP0HVJ5BD8 XXXXXX4321	RECURRING TRANSFER FROM
RECURRING TRANSFER TO NAME SAVINGS REF #OP0HC7LF69 XXXXXXXXX1234	NG TRANSFER TO NAME SAVINGS REF #OP0HC7LF69 XXXXXXXXX1234	RECURRING TRANSFER TO
RECURRING TRANSFER TO NAME SAVINGS REF #OP0HGWGSNR XXXXXXXXX1234	NG TRANSFER TO NAME SAVINGS REF #OP0HGWGSNR XXXXXXXXX1234	RECURRING TRANSFER TO
SQ *RILEY'S TREASURE THRI	EY'S TREASURE THRI	RILEY'S TREASURE
SQ *RILEY'S TREASURE THRI	EY'S TREASURE THRI	RILEY'S TREASURE
ROYAL CARIBBEAN CRUISES	ARIBBEAN CRUISES	ROYAL CARIBBEAN CRUISES
STATE FARM RO 27 SFPP 14 S 120311111 X	STATE FARM RO 27 SFPP 14 S	STATE FARM RO
STATE FARM RO 27 SFPP 14 S 120311111 NAME	STATE FARM RO 27 SFPP 14 S	STATE FARM RO
STORE # 0351 BRANSON MO (P9356000400Y3J7N8)	STORE # 0351 BRANSON MO (	STORE # 0351 BRANSON MO
STORE # 0351 BRANSON MO (P9356001400Y3B2QE)	STORE # 0351 BRANSON MO (	STORE # 0351 BRANSON MO
Subway 13656	13656	Subway
TST* Mr Gilbertis Place	Gilbertis Place	Mr Gilbertis Place
TUESDAY MORNING # 0837	MORNING # 0837	TUESDAY MORNING
TUESDAY MORNING # 0874	MORNING # 0874	TUESDAY MORNING
UNITED WORLD HTH FEB INSPRM 230201 R211J54 NAME2	UNITED WORLD HTH FEB INSPRM	UNITED WORLD HTH
UNITED WORLD HTH JAN INSPRM 230103 R211J54 NAME2	UNITED WORLD HTH JAN INSPRM	UNITED WORLD HTH
WHT RVR FSH HS - BRANSON	FSH HS - BRANSON	WHT RVR FSH HS
WOODHILLS.ORG	LS.ORG	WOODHILLS.ORG

 

[etaf]

thanks for the reply
mmm a lot more complex as you have alpha numeric - some with * some with # and not all extracted

for example
18 INDUSTRIAL PARK DHOLLISTER, MO
is not extracted
some *v or *5

for example
STORE # 0351 BRANSON MO (P9356000400Y3J7N8)
ignores the # and number - bit goes for 2nd number

I will need to think about this, but not around for a few days now - so hopefully another member will answer

Clearly the IFERROR , does not work

 

[SerenityNetworks]

I will need to think about this, but not around for a few days now - so hopefully another member will answer

Thanks. I'll ponder on it as well, and consider learning about those new text string functions.

 

[etaf]

thanks , and perhaps any rules that can be applied to the string - maybe a VBA solution (which i do not provide on forums) may be the way to go in the end