[Solved] String Data Entry
-
- Posts: 24
- Joined: Mon Apr 08, 2019 3:38 pm
[Solved] String Data Entry
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.
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.
Last edited by robleyd on Thu Apr 11, 2019 1:13 pm, edited 3 times in total.
Reason: Add green tick
Reason: Add green tick
OpenOffice 4.1.3 on Windows 7
Re: String Data Entry
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
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
Code: Select all
=IF(ISERROR(FIND("Total:";A1));"";VALUE(MID(A1;FIND("Total:";A1)+6;LEN(A1))))
If there are cases where this won't, or might not, be, please provide examples so that the formula can be modified
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.
-
- Posts: 24
- Joined: Mon Apr 08, 2019 3:38 pm
Re: String Data Entry
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.
OpenOffice 4.1.3 on Windows 7
-
- Posts: 24
- Joined: Mon Apr 08, 2019 3:38 pm
Re: String Data Entry
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.
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.
OpenOffice 4.1.3 on Windows 7
Re: String Data Entry
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.
If you can guarantee that, then I have ideas...if you can't, then it's going to make the task a lot harder.
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.
-
- Posts: 24
- Joined: Mon Apr 08, 2019 3:38 pm
Re: String Data Entry
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.
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.
OpenOffice 4.1.3 on Windows 7
-
- Posts: 24
- Joined: Mon Apr 08, 2019 3:38 pm
Re: String Data Entry
I really appreciate your help, Russel. Thank you.
OpenOffice 4.1.3 on Windows 7
-
- Posts: 24
- Joined: Mon Apr 08, 2019 3:38 pm
Re: String Data Entry
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.
OpenOffice 4.1.3 on Windows 7
Re: String Data Entry
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.
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.
-
- Posts: 24
- Joined: Mon Apr 08, 2019 3:38 pm
Re: String Data Entry
I'll look forward to it, Russel. Thank you for your help. Greatly appreciated.
OpenOffice 4.1.3 on Windows 7
Re: String Data Entry
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.
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.
- Attachments
-
- AOO 97637.ods
- (8.88 KiB) Downloaded 120 times
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: String Data Entry
Hello,
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:
I'm not sure what it means: Next to "Total:" are several things possible, e. g.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.
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:
How can I detect where the "data next to Total:" ends?Expenses breakdown Expense 1: 10 Expense 2: 15 Total: 25 Limit: 20 Remaining: -5 Result: Over the Limit
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
-
- Posts: 24
- Joined: Mon Apr 08, 2019 3:38 pm
Re: String Data Entry
Hi Mikele,
You are right on your example. There are some instances where I may input special characters, not just pure numeric.
You are right on your example. There are some instances where I may input special characters, not just pure numeric.
OpenOffice 4.1.3 on Windows 7
Re: String Data Entry
Hi,
and how can I detect where the "data next to Total:" ends?
and how can I detect where the "data next to Total:" ends?
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
-
- Posts: 24
- Joined: Mon Apr 08, 2019 3:38 pm
Re: String Data Entry
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.
- Attachments
-
- Untitled 1.ods
- (9.1 KiB) Downloaded 104 times
OpenOffice 4.1.3 on Windows 7
-
- Posts: 24
- Joined: Mon Apr 08, 2019 3:38 pm
Re: String Data Entry
Here's what it looks like in notepad before I paste it into the cell...
- Attachments
-
- notepaddraft.txt
- (89 Bytes) Downloaded 101 times
OpenOffice 4.1.3 on Windows 7
Re: String Data Entry
Hi,
try this
try this
Code: Select all
=MID(A1;SEARCH("Total:";A1)+6;SEARCH(CHR(10);A1;SEARCH("Total:";A1)+6)-SEARCH("Total:";A1)-6)
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
-
- Posts: 24
- Joined: Mon Apr 08, 2019 3:38 pm
Re: String Data Entry
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.
OpenOffice 4.1.3 on Windows 7
Re: String Data Entry
Hi,
sorry - wrong translation
sorry - wrong translation
Code: Select all
=MID(A1;SEARCH("Total:";A1)+6;SEARCH(CODE(10);A1;SEARCH("Total:";A1)+6)-SEARCH("Total:";A1)-6)
- Attachments
-
- sample2.ods
- (20.28 KiB) Downloaded 117 times
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
-
- Posts: 24
- Joined: Mon Apr 08, 2019 3:38 pm
Re: String Data Entry
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?
OpenOffice 4.1.3 on Windows 7
Re: String Data Entry
Hi,
RusselB already gave you a solution for this:
and now
RusselB already gave you a solution for this:
Code: Select all
=IF(ISERROR(FIND("Total:";A1));"";VALUE(MID(A1;FIND("Total:";A1)+6;LEN(A1))))
Code: Select all
=IF(ISERROR(FIND("Total:";A1));"";MID(A1;SEARCH("Total:";A1)+6;SEARCH(CODE(10);A1;SEARCH("Total:";A1)+6)-SEARCH("Total:";A1)-6))
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
- MrProgrammer
- Moderator
- Posts: 4905
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: String Data Entry
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.jhonpaul72 wrote:I want to have the Cell A2 blank if the "Total:" string is not present/detected, instead of #VALUE!.
[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
-
- Posts: 24
- Joined: Mon Apr 08, 2019 3:38 pm
Re: String Data Entry
Thank you guys for all of your help. I got what I needs. I really appreciate the help.
OpenOffice 4.1.3 on Windows 7