Page 1 of 1

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

PostPosted: Sat May 02, 2020 3:40 pm
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

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

PostPosted: Sat May 02, 2020 4:10 pm
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.

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

PostPosted: Sat May 02, 2020 4:18 pm
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

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

PostPosted: Sat May 02, 2020 5:22 pm
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.

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

PostPosted: Sat May 02, 2020 7:07 pm
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.

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

PostPosted: Sat May 02, 2020 10:10 pm
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).

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

PostPosted: Sun May 03, 2020 6:36 am
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.

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

PostPosted: Sun May 03, 2020 1:30 pm
by Shane-001
[Solved] Thank you all.

Best Regards