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
A formula to look for keywords and extract values.
-
- Posts: 11
- Joined: Sun Nov 25, 2018 2:32 am
A formula to look for keywords and extract values.
OpenOffice 4.1.3 on macOS Sierra 10.12.6 (16G1510)
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.
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.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
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.
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
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 71 times
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
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.
Something that might work: download/file.php?id=136
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 11
- Joined: Sun Nov 25, 2018 2:32 am
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!
Thanks!
OpenOffice 4.1.3 on macOS Sierra 10.12.6 (16G1510)
Re: A formula to look for keywords and extract values.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
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.
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 96 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
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)
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?
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 87 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice