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

Discuss the spreadsheet application

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

Postby Shane-001 » Sat May 02, 2020 3:40 pm

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

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

Postby FJCC » Sat May 02, 2020 4:10 pm

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7784
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby Shane-001 » Sat May 02, 2020 4:18 pm

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

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

Postby FJCC » Sat May 02, 2020 5:22 pm

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7784
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby RusselB » Sat May 02, 2020 7:07 pm

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

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

Postby floris v » Sat May 02, 2020 10:10 pm

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).
AOO 4.1.6 op Linux Mint
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
floris v
Volunteer
 
Posts: 4206
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

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

Postby Zizi64 » Sun May 03, 2020 6:36 am

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; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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.
User avatar
Zizi64
Volunteer
 
Posts: 9580
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Shane-001 » Sun May 03, 2020 1:30 pm

[Solved] Thank you all.

Best Regards
OpenOffice 4.1.7 on Windows 10
Shane-001
 
Posts: 9
Joined: Tue Apr 07, 2020 1:08 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 23 guests