A formula to look for keywords and extract values.

[avaizhashmi]

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

[RusselB]

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.

[RPG]

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

[Villeroy]

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.

Something that might work: download/file.php?id=136

[avaizhashmi]

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

Thanks!

[Villeroy]

Programmers don't use spreadsheets.

[Lupp]

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.

[Villeroy]

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)
Call menu:Data>Refresh

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?