Hi all. I'm pretty new to spreadsheets so I am a complete novice. I have learned how to merge text and numbers in to one cell but how can I do maths with them when all I am getting is #value! I have looked all over the net with no success. Here is an example to make it clearer.
Man Utd 2.12 * Liverpool 2.00 Obviously this sum should equal 4.24 but instead I get the dreaded #value. I think I need to have the calculation as "read only the numbers". How do I do this?
Kind Regards
[Solved] Ignore text in the same cell that also has numbers
[Solved] Ignore text in the same cell that also has numbers
Last edited by Shane-001 on Sun May 03, 2020 1:32 pm, edited 1 time in total.
OpenOffice 4.1.7 on Windows 10
Re: Ignore text in the same cell that also has numbers.
It is probably possible to pluck out the numbers embedded among the words and do calculations but the far better approach is to keep the numbers and text separate. The usual approach is to use a column for the text labels and the adjacent column for the numeric value. If you can explain more about what you need to do, we can probably give detailed guidance.
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: Ignore text in the same cell that also has numbers.
Thank you for your reply.
It has to have the text and the number together because the cell will be copied somewhere else on the spreadsheet The text and number are just part of a calculation and I need to know which text ( Football Team) is what. So it is adamant they stay together.
Kind Regards
It has to have the text and the number together because the cell will be copied somewhere else on the spreadsheet The text and number are just part of a calculation and I need to know which text ( Football Team) is what. So it is adamant they stay together.
Kind Regards
OpenOffice 4.1.7 on Windows 10
Re: Ignore text in the same cell that also has numbers
You described yourself as a novice with spreadsheets but you are insisting that the difficult method you are trying to use is the only possible solution to your problem. I started using spreadsheets more the 25 years ago and I have never encountered a situation where mixing text and numbers in this way was required or even a good idea. I will admit that it is possible that you have a new class of problem and I am about to learn something. I hope you are willing to admit that the path you have started on may not be the best one. Please upload a file showing a sample of your data (before you combine the numbers and text!) and explain what your ultimate goal is. So far, I understand that you want to sum numbers associated with football clubs and use them elsewhere in the same document.
To upload a file, click Post Reply and look for the Upload Attachment tab just below the box where you type a response.
To upload a file, click Post Reply and look for the Upload Attachment tab just below the box where you type a response.
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: Ignore text in the same cell that also has numbers
Quick suggestion based on what I've read, is to extract the numbers into helper columns, probably using a regex code, then do the calculations using the information in the helper columns.
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: Ignore text in the same cell that also has numbers.
You can have the text and numbers in adjacent columns for the math, and copy both to one cell somewhere else with the formula "= B20&C20" (and 20 is just an example here).Shane-001 wrote:Thank you for your reply.
It has to have the text and the number together because the cell will be copied somewhere else on the spreadsheet The text and number are just part of a calculation and I need to know which text ( Football Team) is what. So it is adamant they stay together.
Kind Regards
OpenOffice 4.1.11 on Ubuntu; LibreOffice 6.4 on Linux Mint, LibreOffice 7.6.2.1 on Ubuntu
If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar.
Nederlandstalig forum
If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar.
Nederlandstalig forum
Re: Ignore text in the same cell that also has numbers
You are sitting on a horse back backwards. (Hungarian proverb)It has to have the text and the number together because the cell will be copied somewhere else on the spreadsheet The text and number are just part of a calculation and I need to know which text ( Football Team) is what. So it is adamant they stay together.
Store the number and the text separatelly and concatenate them by a formula, if you need see them together in an another cell.
Then you will able calculate with the number without any hocus-pocus.
This is the easier way. Separate (parse) the number/s/ from a mixed content is always a harder task.
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: Ignore text in the same cell that also has numbers
[Solved] Thank you all.
Best Regards
Best Regards
OpenOffice 4.1.7 on Windows 10