A formula to look for keywords and extract values.

Discuss the spreadsheet application

A formula to look for keywords and extract values.

Postby avaizhashmi » Sat Dec 01, 2018 10:52 pm

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.

Postby RusselB » Sun Dec 02, 2018 5:10 am

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
Volunteer
 
Posts: 4867
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Postby RPG » Sun Dec 02, 2018 12:29 pm

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
Keep your data simple
(12.46 KiB) Downloaded 10 times
LibreOffice 6.1.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2159
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

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

Postby Villeroy » Sun Dec 02, 2018 1:09 pm

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26379
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby avaizhashmi » Sun Dec 09, 2018 12:14 am

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.

Postby Villeroy » Sun Dec 09, 2018 12:22 am

Programmers don't use spreadsheets.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26379
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Lupp » Sun Dec 09, 2018 1:20 am

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
(24.6 KiB) Downloaded 7 times
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2339
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby Villeroy » Sun Dec 09, 2018 4:04 pm

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?
Attachments
typefilter2.ods
(24.91 KiB) Downloaded 7 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26379
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 25 guests