[Solved] Parsing Numeric Values

[KirkWard]

How would I parse numeric data found in a cell, if the data is comma separated?

Examples:
1,2,3,4
1,2,3
2,5,,23,19
19,24,8,6,2

I want to parse each value and individually process it through a formula, and then add the results of each iteration.

Hope that makes sense. No spreadsheet developed as yet. Looking for a starting place.

EDIT: I guess there is not a simple formulaic answer for my need, so I will be using the clunky method I presented lower in this thread. Thanks for looking and the feedback.

[FJCC]

The best approach is to use the menu Data -> Text to Columns and set the separator to be the comma. Each value will then be in its own cell and you can precede without the difficulties of parsing.

[KirkWard]

I am familiar with the text to columns, but as this is a single cell in an exiting table that will be reused, I do not want to reformat the table to include extra columns at multiple points in the table and then have to remove the extra columns before the next recalculation due to a change in variables. It would be a formatting nightmare.

What I need specifically to do is to calculate the interest payment for multiple mortgages, all with the same initial principal, and then add that amount into a grand total. The comma separated numbers are generated by concatenating the column number where a new mortgage is added to the principal, and then calculating the interest payment of the prior notes, based on what column they were entered into at.

Code: Select all

=CONCATENATE(1;",";2;",";3;",";4)

as an example from above.

Since the mortgage acquisition months (or columns) are generated dynamically, the concatenated value is what has to be read and parsed, or put another way, where the individual values have to be used in a formula to calculate interest.

[KirkWard]

I just stumbled across something called an INLINE ARRAY formula or such. Is it possible I could concatenate these values into an array and apply the IPMT and PPMT formulas to the resulting principal? Can anyone suggest the structure of a formula to achieve this?

[Lupp]

Inline arrays need to be constant arrays and cannot be passed as parameters as if they are strings.
In short: Inline arrays are of no use for your purpose.

The fundamental problem in the context is that OpenOffice doesn't have a standard function for splitting lists. Even LibO which has a TEXTJOIN() for a long time now does not offer a corresponding TEXTSPLIT().

In other words: You need to either / or
-1- write and use custom functions
-2- do the splitting interactively as FJCC suggested.

Concerning the option to do it based on user code you may check out the attached example. It was designed under LibO 6.3.3.1 but also works for me in AOO 4.1.5. However, the BASIC function ISNUMERIC has flaws. IMO, and there may be additional problems not yet detected.

BTW 1: You shouldn't use the comma as the list separtator together with numbers. The majority of locales use the comma as their decimal separator.
BTW 2: The version of AOO given in your signature is very old now. If you actually are still using it you may consider to update to a recent version. Otherwise you should update your signature.

[KirkWard]

I tried to understand those functions, but was unable to. They are way above my head.

Is it possible to create dynamic arrays?

[Villeroy]

This is a spreadsheet. It calculates with cell values. A script language could do whatever you want with simple text files.
Similar recent topic: viewtopic.php?f=9&t=99446

[KirkWard]

Well, I guess with my limited skillset, and the limitations of the spreadsheet (downloaded from the SBA/SCORE website, for a complicated business model, I guess I will need to find some way outside of OO/LO calc. Sad to me because I like working in a spreadsheet, even though I don't know enough about all the functions.

Thanks for all your help and here's another copy of the sheet, at my current stage of play, if anyone has any suggestions, and can instruct me, I would appreciate it.

Thanks for all the help so far,
Kirk

[Villeroy]

This is only a problem because you refuse to use the text-to-column tool.

[Zizi64]

Why you not use the built-in features as Villeroy suggested?

...Or write your own parse macro in StarBasic...

[KirkWard]

As Villeroy said, it is a problem because I refuse to use the text to columns tool. But as I said earlier, if not here, then in another thread, I do not want to use that tool because of the structure of the spreadsheet and the workbook, which is over twenty pages in size and very interlinked. And because the spreadsheets will be reviewed and worked with by someone even less experienced than I. Someone with fingers that can hit keys they should be staying away from.

That being said, after I went to bed last night (I am in the Eastern US Time Zone), I was able to think through what I think is a solution. The solution is to add a new sheet, make it a hidden sheet (which I hope OO/LO can do), and then develop tables where I concatenate a calculated variable from the visible spreadsheet, then in a series of cells below each variable, use one of the LEN function variants to pull out the data I need and calculate my IPMT and PPMT answers in that column, and then sum the column. Clunky, but probably doable, and useful because it can be hidden from flying fingers.

Will work on it after breakfast, and if it works, I will mark the thread solved. If not, I will come back, show you what I did, and whine some more.

KW

[Lupp]

To me this sounds again as if you intend to first concatenate numeric cell contents (or results) into a list, and then to extract the single numbers from that list one by one to perform a final calculation.

WHY don't you simply reference the original cells when calculating? - Or in what way did I fundamentally misunderstand you?

BTW1 (once again): Recent versions of LibO come with the TEXTJOIN() function which creates separated lists.
BTW2: Extracting from lists based on lengths requires a valid assurance concernig those lengths.

[KirkWard]

To me this sounds again as if you intend to first concatenate numeric cell contents (or results) into a list, and then to extract the single numbers from that list one by one to perform a final calculation.

WHY don't you simply reference the original cells when calculating? - Or in what way did I fundamentally misunderstand you?

BTW1 (once again): Recent versions of LibO come with the TEXTJOIN() function which creates separated lists.
BTW2: Extracting from lists based on lengths requires a valid assurance concernig those lengths.

I doubt you misunderstood me, most likely I misstated. I probably am in so far I can't see the forest for the trees, so I will try to extricate myself by working with references to initial cells. Makes sense, but I am really having a problem with this. I'll also look at TEXTJOIN to see what it does. Help is appreciated.

As an additional note, one of the restrictions I have is presentation. I am trying to keep the layout presentable to the viewers, which is why I was avoiding the Text to Columns utility, as they absolutely would not understand that.

EDIT: I have only installed LO a few days ago, so I presume Version: 6.2.7.1 (x64) is the latest. I cannot find an online help reference for TEXTJOIN, and cannot figure out how to install the desktop help.

[Villeroy]

Ctrl+F2
Search TEXTJOIN

You get the offline help package for 6.2 from the download page, however the documentation of this particular function has been added recently to version 6.3 although it exists much longer.

[Zizi64]

EDIT: I have only installed LO a few days ago, so I presume Version: 6.2.7.1 (x64) is the latest.

The latest LibreOffice STILL version is the 6.2.8 now.

[KirkWard]

What is STILL ?

[Zizi64]

https://www.libreoffice.org/download/download/

[Zizi64]

Your signature is:

_____________________________
OpenOffice 3.2 on Windows 7

Please update it in your user profile in this forum.

[Zizi64]

https://www.libreoffice.org/download/release-notes/

[KirkWard]

I interpret that to mean that STILL is the latest stable release, so called because it is now "still?"

Going back to my original post, I have attached something that gives me the results I desire for my first year. I am thinking the second and third years of the forecast would simply be copies of this (to be) hidden sheet, modified to continue note counts from the first page, which is a whew type of endeavour, but I must plod on.

If aanyone can privide a simpler way of accomplishing this that can handle up to five years, I could use help. I haven't used spreadsheets in decades and it is a bit overwwhelming at my age with as much as has to be included in this project.

I plod because I can no longer run (more than a few yards anyway), so plod on I must. Help accepted, but I do need a lot of explanations.

Here's my work, clunky as it is.

EDIT: Saved my work, uploaded it, reopened my local and it was all discombobulated. This upload is broken. Will work and replace.

[KirkWard]

Have edited my profile a bit. Do I need to add more? If so, what?

Thanks

[Zizi64]

Do I need to add more? If so, what?

Your AOO/LO version/s/ and the Operating System version/s/

[Villeroy]

I interpret that to mean that STILL is the latest stable release, so called because it is now "still?"

It is the second oldest version still supported with bug fixes. 6.0, 6.1, 6.2, 6.3 etc are different versions with different features. The third number .7 in 6.2.7 means that this is the 7th bug fix release of version 6.2. Bugs are constantly fixed and the fixes are mounted into the bug fix releases of the current releases 6.2 (still) and 6.3 (fresh). Some day 6.4 or 7.0 will be the latest shit and 6.3 will be the second oldest version still supported with bug fixes.

[KirkWard]

Bock to the original topic, parsing numbers and calculating monthly interest and principal payments for a varying series of notes. Whew, long subject not totally reflected in the title.

I worked out a clunky method of solving my problem, as shown in the attached sheets extracted from the workbook I am filling in for my presentation.

In this method, on my hidden sheet, I do some concatenation with the CONCATENATE function and then parsing using the MID function to get total monthly interest payments, which is modified whenever the number of new notes is changed in a month. I know it is clunky, but it is the only way I could visualize the interrelationships.

I then reference the SUM of the interest payments each month into the proper position in the presentation regarding commercial mortgages, and do some simple math to calculate the principal payment and then the remaining balances total.

It was asked why I didn't use cell references in my formulas on the presentation page, which I am sure would have been simpler and faster in creating a solution, and the simple answer is that I could not visualize how the cell references would be coded. Even after looking at my clunky solution, I can't recognize a way to generate the pattern that I have. If anyone can provide me a simpler solution, I will be happy to use it. Meanwhile, this is what I have to use.

To experiment with it, change the number of notes in any month (inputs can be done on the rows in the blue background. These are created formulaicly in the full workbook, but references were deleted when I extracted these sheets, so the entries are manual for demonstration purposes.

Please take a look and give me some feedback.

Thanks,
Kirk Ward