[Solved] Ignore text in the same cell that also has numbers

Discuss the spreadsheet application
Post Reply
Shane-001
Posts: 9
Joined: Tue Apr 07, 2020 1:08 pm

[Solved] Ignore text in the same cell that also has numbers

Post by Shane-001 »

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
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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Ignore text in the same cell that also has numbers.

Post by FJCC »

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.
Shane-001
Posts: 9
Joined: Tue Apr 07, 2020 1:08 pm

Re: Ignore text in the same cell that also has numbers.

Post by Shane-001 »

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.7 on Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Ignore text in the same cell that also has numbers

Post by FJCC »

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

Re: Ignore text in the same cell that also has numbers

Post by RusselB »

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.
User avatar
floris v
Volunteer
Posts: 4423
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

Re: Ignore text in the same cell that also has numbers.

Post by floris v »

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
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).
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
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Ignore text in the same cell that also has numbers

Post by Zizi64 »

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.
You are sitting on a horse back backwards. (Hungarian proverb)

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.
Shane-001
Posts: 9
Joined: Tue Apr 07, 2020 1:08 pm

Re: Ignore text in the same cell that also has numbers

Post by Shane-001 »

[Solved] Thank you all.

Best Regards
OpenOffice 4.1.7 on Windows 10
Post Reply