[Solved] String Data Entry

Discuss the spreadsheet application

[Solved] String Data Entry

Postby jhonpaul72 » Mon Apr 08, 2019 4:50 pm

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

Re: String Data Entry

Postby RusselB » Mon Apr 08, 2019 7:05 pm

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   Expand viewCollapse view
=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.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5399
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: String Data Entry

Postby jhonpaul72 » Mon Apr 08, 2019 7:14 pm

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

Re: String Data Entry

Postby jhonpaul72 » Mon Apr 08, 2019 7:27 pm

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

Re: String Data Entry

Postby RusselB » Tue Apr 09, 2019 2:51 am

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.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5399
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: String Data Entry

Postby jhonpaul72 » Tue Apr 09, 2019 10:59 am

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

Re: String Data Entry

Postby jhonpaul72 » Tue Apr 09, 2019 11:19 am

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

Re: String Data Entry

Postby jhonpaul72 » Tue Apr 09, 2019 2:28 pm

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

Re: String Data Entry

Postby RusselB » Tue Apr 09, 2019 2:47 pm

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.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5399
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: String Data Entry

Postby jhonpaul72 » Tue Apr 09, 2019 4:06 pm

I'll look forward to it, Russel. Thank you for your help. Greatly appreciated.
OpenOffice 4.1.3 on Windows 7
jhonpaul72
 
Posts: 21
Joined: Mon Apr 08, 2019 3:38 pm

Re: String Data Entry

Postby RusselB » Wed Apr 10, 2019 2:25 am

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 12 times
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5399
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: String Data Entry

Postby mikele » Wed Apr 10, 2019 8:03 am

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 on LinuxMint/WinXP/Win7
mikele
 
Posts: 45
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: String Data Entry

Postby jhonpaul72 » Wed Apr 10, 2019 11:31 am

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

Re: String Data Entry

Postby mikele » Wed Apr 10, 2019 11:38 am

Hi,
and how can I detect where the "data next to Total:" ends?
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 45
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: String Data Entry

Postby jhonpaul72 » Wed Apr 10, 2019 11:52 am

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

Re: String Data Entry

Postby jhonpaul72 » Wed Apr 10, 2019 12:18 pm

Here's what it looks like in notepad before I paste it into the cell...
Attachments
notepaddraft.txt
(89 Bytes) Downloaded 11 times
OpenOffice 4.1.3 on Windows 7
jhonpaul72
 
Posts: 21
Joined: Mon Apr 08, 2019 3:38 pm

Re: String Data Entry

Postby mikele » Wed Apr 10, 2019 2:05 pm

Hi,
try this
Code: Select all   Expand viewCollapse view
=MID(A1;SEARCH("Total:";A1)+6;SEARCH(CHR(10);A1;SEARCH("Total:";A1)+6)-SEARCH("Total:";A1)-6)
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 45
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: String Data Entry

Postby jhonpaul72 » Wed Apr 10, 2019 2:13 pm

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

Re: String Data Entry

Postby mikele » Wed Apr 10, 2019 2:22 pm

Hi,
sorry - wrong translation :crazy:
Code: Select all   Expand viewCollapse view
=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 12 times
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 45
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: String Data Entry

Postby jhonpaul72 » Wed Apr 10, 2019 4:09 pm

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

Re: String Data Entry

Postby mikele » Wed Apr 10, 2019 9:34 pm

Hi,
RusselB already gave you a solution for this:
Code: Select all   Expand viewCollapse view
=IF(ISERROR(FIND("Total:";A1));"";VALUE(MID(A1;FIND("Total:";A1)+6;LEN(A1))))

and now
Code: Select all   Expand viewCollapse view
=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 on LinuxMint/WinXP/Win7
mikele
 
Posts: 45
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: String Data Entry

Postby MrProgrammer » Wed Apr 10, 2019 11:08 pm

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 10 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.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3812
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: String Data Entry

Postby jhonpaul72 » Thu Apr 11, 2019 12:37 pm

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


Return to Calc

Who is online

Users browsing this forum: JeJe and 39 guests