How to ignore text in cell, and cal only numbers
How to ignore text in cell, and cal only numbers
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
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
Re: How to ignore text in cell, and cal only numbers
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:
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.
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))
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
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Re: How to ignore text in cell, and cal only numbers
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?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: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.Code: Select all
=VALUE(LEFT(A1;SEARCH(" ";A1)-1))
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
Re: How to ignore text in cell, and cal only numbers
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
Re: How to ignore text in cell, and cal only numbers
Nah, it just gives value error from letgoMatareuz wrote:Maybe this can work for you.
Code: Select all
=SUMPRODUCT(IFERROR(VALUE(LEFT(J2:L2;SEARCH(" ";J2:L2)-1));0))
OPENOFFICE 4.1.7 Windows10
Re: How to ignore text in cell, and cal only numbers
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
Re: How to ignore text in cell, and cal only numbers
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
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: How to ignore text in cell, and cal only numbers
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 onlyMatareuz 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.
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 xDFJCC 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 onlyCode: Select all
SUM(A1:A100)
OPENOFFICE 4.1.7 Windows10
Re: How to ignore text in cell, and cal only numbers
Ahhhh that's right! that's right!skyliner wrote: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 onlyMatareuz 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.
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 ). I'm talking about the "J2:L2" into the function.
LibreOffice 6.3.2.2 Windows 7 Ultimate
Re: How to ignore text in cell, and cal only numbers
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to ignore text in cell, and cal only numbers
In the AOO:Just a frind who use OpenOffice could clear the question if OpenOffice have a function who makes the same of the "IFERROR" of LibreOffice.
Code: Select all
IF(ISERROR(condition;result when true;result when false))
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.
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.
Re: How to ignore text in cell, and cal only numbers
How to use this, anyone? xDrobleyd 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: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.Code: Select all
=VALUE(LEFT(A1;SEARCH(" ";A1)-1))
OPENOFFICE 4.1.7 Windows10
Re: How to ignore text in cell, and cal only numbers
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to ignore text in cell, and cal only numbers
Spreadsheets contain datas (textual and numeric data) and formulas: for manage the text contents and for calculate with the numbers.How to use this, anyone? xD
Code: Select all
=VALUE(LEFT(A1;SEARCH(" ";A1)-1))
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.
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.