## A formula to look for keywords and extract values.

### A formula to look for keywords and extract values.

Hey guys, with your help and a lot of tinkering. I figured out a formula for my needs.

I have a list(cells) of keywords, eg:

A1 = "cashback:"
A2 = "charity:"
A3 = "tips:"

and I have Notes from MINT, the online budgeting app, where under the Notes section, the entries look like

cashback:40; tips:3; charity:10;

Each keyword ends with a ";"

This formula, where notes column is defined as a "Range"

=IF(ISNUMBER(SEARCH(\$A\$1;Range));MID(Range;SEARCH(\$A\$1;Range)+LEN(\$A\$1); SEARCH(";";Range;SEARCH(\$A\$1;Range))-SEARCH(\$A\$1;Range)-LEN(\$A\$1)); "No")

... let's me grab the values of each keyword and put it in different columns ie

cashback --- etc --- etc --- etc
40 --- value --- value --- value

Give it a try its pretty neat and you have suggestions to improve /simplify it

One thing for sure, I would like to avoid repeating the same formula on a million cells. Is there a way around it. But! its working which is huge for a beginner like me. Thanks for your support! I am learning a lot!

A
OpenOffice 4.1.3 on macOS Sierra 10.12.6 (16G1510)
avaizhashmi

Posts: 11
Joined: Sun Nov 25, 2018 2:32 am

### Re: A formula to look for keywords and extract values.

If your "million cells" is even 100x what you actually have, you're going to run into trouble with Calc freezing up on you while it's performing calculations, which means that you can't do anything else in Calc.
It may mean that Calc will crash while frozen, but you'll have no way of knowing if, or when, that'll happen.
If you have even close to 1,000 cells that will need that formula, I'm thinking that a database would be a good idea.

That said, considering the number of times you have SEARCH(\$A\$1;Range) in that formula, you might want to consider using a helper column to hold the results of that search.
Using the helper column means that the search only has to be done once for your entire formula, rather than four times, as you currently have it, making your formula more efficient.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 5188
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: A formula to look for keywords and extract values.

I agree with RusselB. I think it is better to do it in a database but it is maybe possible to start first to collect the data in a more simple spreadsheet. It is maybe a good idea to order the data first by hand and later with other tools.
Look to the inserted rows. I use the columns Date and "Original Description" to keep together the original data. Maybe you have to find an other solution.

When you have order the data in a good way then a pivot table is good for analysing. When it must be a nice looking table getpivotdata can help you.

Romke
Attachments
DataOnMultipleSheetsRPG01.ods
LibreOffice 6.1.5.2 on openSUSE Leap 15
RPG
Volunteer

Posts: 2165
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

### Re: A formula to look for keywords and extract values.

Processing aggregated database data (MINT reports) with a totally inadequate tool (spreadheet) is a waste of time.
Either you don't understand how to get your info out of MINT or MINT is not good enough.

Anyway, instead of selecting subcategories from sheet tabs, you should keep everything in one list and set up a regex filter.

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 26868
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: A formula to look for keywords and extract values.

I am gonna show these responses to my roommate who is a programmer and has sheets working. (its going over my head atm)

Thanks!
OpenOffice 4.1.3 on macOS Sierra 10.12.6 (16G1510)
avaizhashmi

Posts: 11
Joined: Sun Nov 25, 2018 2:32 am

### Re: A formula to look for keywords and extract values.

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 26868
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: A formula to look for keywords and extract values.

Well, I don't know the MINT "app" for budgeting or whatever and thus I'm in danger to also just mumble a prejudice: I would not expect a smartphone app to be serious software but a means to help selling smartphones and to participate in the earnings from that promised land.

With the accent on testing the next version 6.2.0 of LibreOffice with its new REGEX() function I made a sheet from this thread (and the 96009 predecessor) a few days ago. If someone also wants to test that I attach the file here. It will not work under LibO 6.1 or under AOO, but in LibO 6.2 it might demonstrate some of the basic issues I would ecxpect anyway as long as I not have clear assurances concerning the MINT notes to evaluate.
Attachments
aoo96009SplitByREGEX_2.ods
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2491
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

### Re: A formula to look for keywords and extract values.

Just in case anybody wonders how my "typefilter.ods" is related to this:
I redefined the database range. It points to A4:A24 now without any link to the Bibliography database
I replaced the text box with a combo box. This is a text box with auto-compete, a tiny little extra but not important.
I added a colon to the regex formula in F2.
I added a calculated column extracting the numbers behind the colons.
I added a bunch of SUBTOTAL formulas to get aggregations from the filtered data.

For some reason I don't know yet, the macro does not work properly with LibreOffice 6. The filter hides all rows.

It works pretty well with all office suites, without any macro and without form control:
Edit the linked cell D2 directly (I added a validation list to it)
Click any cell in database range Import1 (A4:A24)

I aggree with Lupp that managing your finances with an online app is foolish. How can you trust such thing? And what is it worth if you still need a spreadsheet to get the information you need?
Attachments
typefilter2.ods
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 26868
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany