Apache OpenOffice Community Forum

Hi,

Hope someone can help me. I'm trying to find a function/formula or macro if ever or I hope this is even possible. Here's the catch for example. On Cell A1. IF there's a string detected like for example "Total:". All information entered on A1 is this..."Expense 1: 10 Expense 2: 15 Total: 25". The data will be entered in A2 is "25" whenever the cell A1 detected the string "Total:". If that is not detected, then no data will be filled in A2. The next string in "Total:" will be entered in cell A2.

Welcome to the Forums.
I'd suggest having the amount beside Total be in B1, rather than A2. Having a mixture in one column can lead to many more problems down the road.
I used some helper columns to make the generation of this formula easier. The formula I came up with is Note: There can only be 1 occurrence of Total: in the string and it must be at the end, as you showed in your example.
If there are cases where this won't, or might not, be, please provide examples so that the formula can be modified

Oh yeah. I forgot. It should be B1. I was confused for a bit back there. I'll check your resolution. Thanks for making a clarification.

Also Russel, In my case. To be honest, I may have provided a broad example. This String that needs to be detected always at the middle of the information entered into a cell. For example:
Expenses breakdown
Expense 1: 10
Expense 2: 15
Total: 25
Limit: 20
Remaining: -5
Result: Over the Limit


...I'm so sorry to bother. There are several reasons why I don't want to have each one of the information entered on separate cells. That's why I came up with this question.

OK.. I can re-work my formula, but I need to know one thing, is there a guaranteed character (example a space) that will come after the number that you want returned from the string that is proceeded by Total:
If you can guarantee that, then I have ideas...if you can't, then it's going to make the task a lot harder.

No space. Just the fixed format/arrangement or whatever you call it.
Just "Total: (number)". I know it will be a lot harder if there's some space or additional character after the number. But no, that's it.

I really appreciate your help, Russel. Thank you.

I've tried the first formula you suggested. It worked on the first example I provided. But I've tried to enter a data next to total that is not a numeric data like for example "N/A" and it returned err:502. Which made me think this formula only works on numbers. Or unless there needs some cell formatting to do. Well I need to have the exact data entered next to "Total:" returned in Cell B1 whether it is numeric or alphanumeric or numeric with symbol (dash or hyphen to be more specific) in between like "4-19". I hope this is possible.

This is possible, but not simple. I will work on a few formulas that I have in mind and post back when I have one that works...probably later today.

I'll look forward to it, Russel. Thank you for your help. Greatly appreciated.

Sorry this took so long.
I was unable to do this in a single formula that was still easy to read, so I use 3 helper columns (it was more).
In order for my solution to work, you must have Enable regular expressions in formulas checked. This setting is under Tools -> Options -> OpenOffice Calc -> Calculate
The attached spreadsheet uses your second example as the basis.

Hello,

Well I need to have the exact data entered next to "Total:" returned in Cell B1 whether it is numeric or alphanumeric or numeric with symbol (dash or hyphen to be more specific) in between like "4-19". I hope this is possible.

I'm not sure what it means: Next to "Total:" are several things possible, e. g.
Total: 25
Total: 4-9
Total: N/A
Total: 25$
...
Right?
You need the exact data entered next to "Total:" - this ist quit difficult to solve if afterwards the text goes on like in your example:

Expenses breakdown Expense 1: 10 Expense 2: 15 Total: 25 Limit: 20 Remaining: -5 Result: Over the Limit

How can I detect where the "data next to Total:" ends?

Hi Mikele,

You are right on your example. There are some instances where I may input special characters, not just pure numeric.

Hi,
and how can I detect where the "data next to Total:" ends?

Russel, I'll go ahead and submit the right data that I'm entering into the cell. Actually before I enter the data into the cell, I write it first in a Notepad, copy the text, double click the cell to open it and paste it there. Below is the sample.

Here's what it looks like in notepad before I paste it into the cell...

Hi,
try this

Hi Mikele, I tried it and it did not work. It only returned #NAME?. I don't know if I'm missing something. But if you can upload a sample file with this included the data I entered, that might help.

Hi,
sorry - wrong translation

That worked. Thank you. I have another question, hope this will not mess up the whole formula. When I erase/remove the string "Total:" on Cell A1, it returned #VALUE!. There will be instances that I will be entering data that has no "Total:" string and BTH, I want to have the Cell A2 blank if the "Total:" string is not present/detected, instead of #VALUE!. Would this be possible?

Hi,
RusselB already gave you a solution for this: and now

jhonpaul72 wrote:I want to have the Cell A2 blank if the "Total:" string is not present/detected, instead of #VALUE!.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

Thank you guys for all of your help. I got what I needs. I really appreciate the help.