[Solved] String Data Entry

Discuss the spreadsheet application
Post Reply
jhonpaul72
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

[Solved] String Data Entry

Post by jhonpaul72 »

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.
Last edited by robleyd on Thu Apr 11, 2019 1:13 pm, edited 3 times in total.
Reason: Add green tick
OpenOffice 4.1.3 on Windows 7
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: String Data Entry

Post by RusselB »

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

Code: Select all

=IF(ISERROR(FIND("Total:";A1));"";VALUE(MID(A1;FIND("Total:";A1)+6;LEN(A1))))
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
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.
jhonpaul72
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: String Data Entry

Post by jhonpaul72 »

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
jhonpaul72
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: String Data Entry

Post by jhonpaul72 »

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.
OpenOffice 4.1.3 on Windows 7
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: String Data Entry

Post by RusselB »

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.
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.
jhonpaul72
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: String Data Entry

Post by jhonpaul72 »

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.
OpenOffice 4.1.3 on Windows 7
jhonpaul72
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: String Data Entry

Post by jhonpaul72 »

I really appreciate your help, Russel. Thank you.
OpenOffice 4.1.3 on Windows 7
jhonpaul72
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: String Data Entry

Post by jhonpaul72 »

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
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: String Data Entry

Post by RusselB »

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.
jhonpaul72
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: String Data Entry

Post by jhonpaul72 »

I'll look forward to it, Russel. Thank you for your help. Greatly appreciated.
OpenOffice 4.1.3 on Windows 7
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: String Data Entry

Post by RusselB »

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.
Attachments
AOO 97637.ods
(8.88 KiB) Downloaded 114 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.
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: String Data Entry

Post by mikele »

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?
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
jhonpaul72
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: String Data Entry

Post by jhonpaul72 »

Hi Mikele,

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
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: String Data Entry

Post by mikele »

Hi,
and how can I detect where the "data next to Total:" ends?
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
jhonpaul72
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: String Data Entry

Post by jhonpaul72 »

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 99 times
OpenOffice 4.1.3 on Windows 7
jhonpaul72
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: String Data Entry

Post by jhonpaul72 »

Here's what it looks like in notepad before I paste it into the cell...
Attachments
notepaddraft.txt
(89 Bytes) Downloaded 96 times
OpenOffice 4.1.3 on Windows 7
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: String Data Entry

Post by mikele »

Hi,
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
jhonpaul72
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: String Data Entry

Post by jhonpaul72 »

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
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: String Data Entry

Post by mikele »

Hi,
sorry - wrong translation :crazy:

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 112 times
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
jhonpaul72
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: String Data Entry

Post by jhonpaul72 »

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
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: String Data Entry

Post by mikele »

Hi,
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))))
and now

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
User avatar
MrProgrammer
Moderator
Posts: 4883
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: String Data Entry

Post by MrProgrammer »

jhonpaul72 wrote:I want to have the Cell A2 blank if the "Total:" string is not present/detected, instead of #VALUE!.
201904101601.ods
"Total: " at beginning, middle, end, or missing in cell
(20.4 KiB) Downloaded 93 times
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
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).
jhonpaul72
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: String Data Entry

Post by jhonpaul72 »

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
Post Reply