How to ignore text in cell, and cal only numbers

Discuss the spreadsheet application
Post Reply
skyliner
Posts: 5
Joined: Fri Jul 16, 2021 5:36 am

How to ignore text in cell, and cal only numbers

Post by skyliner »

Hi, im a noob, i'm googleing my every step for a simple sheet that will help me keep track of my ''money''.
Is there a way for sum / + calculation to ignore letters in the same cell and only add numbers?
Im lazy like that and i want to input something like ''1200 for rent'' and ''150 food'', and the thing to sum up only numbers,
ignoring text and not giving #Value! error. I know you will say its simpler to just seperate the two, but because of the current on-the-go layout i want the text and numbers to be in the same cell, can it be done? Is there a formula of some sort?

It's very rudamentary, im still figuring out what i need it to do. It will look better once i'm all done and start tidying it up a bit ;) A drive for uber & bolt and its really hard for me to keep track of my earnings, so i decided to just count the cash
i have currently in my wallet every week to see where i'm at xD Coz i dont know if im loosing or gaining money xD
OPENOFFICE 4.1.7 Windows10
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How to ignore text in cell, and cal only numbers

Post by robleyd »

The proper way to store your information is in two cells - one the value and the other, the description.

I don't recommend this, but if your current data is always guaranteed to start with one or more digits, followed by a space and some text, the following formula would work, assuming your data to be in A1:

Code: Select all

=VALUE(LEFT(A1;SEARCH(" ";A1)-1))
Unexpected spaces before or among the digits, or no digits, no spaces and quite a few other things may cause an error, or unexpected results.

As you are new to spreadsheets, [Tutorial] Ten concepts that very Calc user should know may be of use to you.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
skyliner
Posts: 5
Joined: Fri Jul 16, 2021 5:36 am

Re: How to ignore text in cell, and cal only numbers

Post by skyliner »

robleyd wrote: I don't recommend this, but if your current data is always guaranteed to start with one or more digits, followed by a space and some text, the following formula would work, assuming your data to be in A1:

Code: Select all

=VALUE(LEFT(A1;SEARCH(" ";A1)-1))
Unexpected spaces before or among the digits, or no digits, no spaces and quite a few other things may cause an error, or unexpected results.
Ye that would work well with what i wanted ;) But, i dont know how to implement it xD I want (example) Cell A1 to sum (or add, how do you say it?) cells ranging from A2:A100, how do i use your formula?
I know i should read and learnt how to do it, but i use this very very rarely and it's a lot of time to find and learn this stuff, so i end up googleing questions people allready asked, and play with it untill i got it working xD
(I menaged to get ISBLANK function working xD , dont really understand how to use it, but it did the job i wanted )
Attachments
Novac stanje.ods
(13.67 KiB) Downloaded 140 times
OPENOFFICE 4.1.7 Windows10
Matareuz
Posts: 23
Joined: Fri Nov 20, 2020 4:33 pm
Location: Venezuela

Re: How to ignore text in cell, and cal only numbers

Post by Matareuz »

Maybe this can work for you.

Code: Select all

=SUMPRODUCT(IFERROR(VALUE(LEFT(J2:L2;SEARCH(" ";J2:L2)-1));0))
LibreOffice 6.3.2.2 Windows 7 Ultimate
skyliner
Posts: 5
Joined: Fri Jul 16, 2021 5:36 am

Re: How to ignore text in cell, and cal only numbers

Post by skyliner »

Matareuz wrote:Maybe this can work for you.

Code: Select all

=SUMPRODUCT(IFERROR(VALUE(LEFT(J2:L2;SEARCH(" ";J2:L2)-1));0))
Nah, it just gives value error from letgo
OPENOFFICE 4.1.7 Windows10
Matareuz
Posts: 23
Joined: Fri Nov 20, 2020 4:33 pm
Location: Venezuela

Re: How to ignore text in cell, and cal only numbers

Post by Matareuz »

When you modify the range of the function, you need modify both J2:L2 and both need to be the same range, otherwise it'll gives error.
LibreOffice 6.3.2.2 Windows 7 Ultimate
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: How to ignore text in cell, and cal only numbers

Post by FJCC »

This is simply a bad idea. The spreadsheet will be MUCH harder to use if you mix numbers and text. The formulas will be very complex and fragile and most of the tools for calculation will not work.
To conveniently enter data with annotations, type the number, press the Tab key (which moves the cursor to the neighboring cell), type the text. Pressing Enter will then move you to the cell under the number so you can easily enter a new value if necessary. Summing A1 through A100 will then require only

Code: Select all

SUM(A1:A100)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
skyliner
Posts: 5
Joined: Fri Jul 16, 2021 5:36 am

Re: How to ignore text in cell, and cal only numbers

Post by skyliner »

Matareuz wrote:When you modify the range of the function, you need modify both J2:L2 and both need to be the same range, otherwise it'll gives error.
Idk what range is :( I formated all these cells the same and put =SUMPRODUCT(iferror(VALUE(LEFT(I3:M3;SEARCH(" ";I3:M3)-1));0)) but error only
FJCC wrote:This is simply a bad idea. The spreadsheet will be MUCH harder to use if you mix numbers and text. The formulas will be very complex and fragile and most of the tools for calculation will not work.
To conveniently enter data with annotations, type the number, press the Tab key (which moves the cursor to the neighboring cell), type the text. Pressing Enter will then move you to the cell under the number so you can easily enter a new value if necessary. Summing A1 through A100 will then require only

Code: Select all

SUM(A1:A100)
ik, tried it now but it takes too much space and i dont like it xD Harder solution is more fun, when someone figures it out for me coz im a noob xD
OPENOFFICE 4.1.7 Windows10
Matareuz
Posts: 23
Joined: Fri Nov 20, 2020 4:33 pm
Location: Venezuela

Re: How to ignore text in cell, and cal only numbers

Post by Matareuz »

skyliner wrote:
Matareuz wrote:When you modify the range of the function, you need modify both J2:L2 and both need to be the same range, otherwise it'll gives error.
Idk what range is :( I formated all these cells the same and put =SUMPRODUCT(iferror(VALUE(LEFT(I3:M3;SEARCH(" ";I3:M3)-1));0)) but error only
Ahhhh that's right! that's right! :oops:

You're using OpenOffice, I'm using LibreOffice and IFERROR work for me, maybe OpenOffice haven't a function with make the seme... I don't kwon.

Just a frind who use OpenOffice could clear the question if OpenOffice have a function who makes the same of the "IFERROR" of LibreOffice.

And about range... I'm sorry, that was a fake friend, I mean rank. (actually, I'm not sure if that's the right word :knock: ). I'm talking about the "J2:L2" into the function.
LibreOffice 6.3.2.2 Windows 7 Ultimate
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to ignore text in cell, and cal only numbers

Post by Villeroy »

Daily growing data collections are stored in databases.
Databases can be filled in the most convenient manner by means of input forms.
The attached database has a table with 3 data columns for date, value and text plus an automatic ID number (row number). You edit that table by means of the input form.
If you omit the date, today's date is inserted when you store a new record.
The text column is meant for repeating categories. Therefore it includes an auto-complete function based on the existing entries.
The value column accepts numbers between 0.01 and 999.99.
The table does not accept any duplicates of date, text and value.
There is a report which lists monthly expenses by categories in a nice print layout.
The input form allows for sorting and filtering of existing data.
A second form allows for quick data entry of new records (tab, type and enter).
You don't have to bother about file formats or storage. Data are stored row by row as you edit.
There are ways to use the stored data in text documents and spreadsheets.
Attachments
Simple_Expense.odb
(29.33 KiB) Downloaded 118 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to ignore text in cell, and cal only numbers

Post by Zizi64 »

Just a frind who use OpenOffice could clear the question if OpenOffice have a function who makes the same of the "IFERROR" of LibreOffice.
In the AOO:

Code: Select all

IF(ISERROR(condition;result when true;result when false))
Of course it works in LO too.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
skyliner
Posts: 5
Joined: Fri Jul 16, 2021 5:36 am

Re: How to ignore text in cell, and cal only numbers

Post by skyliner »

robleyd wrote: I don't recommend this, but if your current data is always guaranteed to start with one or more digits, followed by a space and some text, the following formula would work, assuming your data to be in A1:

Code: Select all

=VALUE(LEFT(A1;SEARCH(" ";A1)-1))
Unexpected spaces before or among the digits, or no digits, no spaces and quite a few other things may cause an error, or unexpected results.
How to use this, anyone? xD
OPENOFFICE 4.1.7 Windows10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to ignore text in cell, and cal only numbers

Post by Villeroy »

Spreadsheets used to be the killer apps of the 80ies. A spreadsheet requires a minimum of skills, particularly when you misuse it as a database replacement which is not what they had been developed for in the first place. As a lazy person let Alexa, Cortana or some other AI record your expenses. These entities can distinguish words from numerals. I'm confident that there is some way to store the numerals and add them up.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to ignore text in cell, and cal only numbers

Post by Zizi64 »

How to use this, anyone? xD
Spreadsheets contain datas (textual and numeric data) and formulas: for manage the text contents and for calculate with the numbers.

Code: Select all

=VALUE(LEFT(A1;SEARCH(" ";A1)-1))
This FORMULA can separate the numeric like content from the textual content, and the result will be appered in the cell where you applied the formula. The input data is located in the cell A1 (in this specific case), for example:

A1:
1200 for rent

Put the furmula into the cell B1. The result of the formula will be a number (1200) in the Cell B1, splitted at the whitespace character (by functions LEFT, SEARCH) and converted from text type into numeric type (by function VALUE).


In my opinion it is much better to input and store the numeric data and the textual name/description in two separated column.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Post Reply