[Excel] Formula Cell with Number and Text

Solved

anixela Posted messages 252 Registration date Status Member Last intervention -
maged - 27 Jan 2021 at 12:49

Good evening,

I am reaching out because I have not been able to find an answer to my problem.

Let me explain:
- Column A can contain either a number or text (which is variable)
- Column B contains a number
- Column C (this is where it gets tricky) should contain: if A is a number, the result of A*B, and if A is text, then we put 0.

My problem is that I don’t know how to translate the following phrase into Excel language: "If Ax is a number then do Ax*Bx otherwise put 0".
I know that I need to use the IF function (condition; value if true; value if false).

Can you help me translate the condition?

Thank you for the help you will provide.

Good night to all
Anixela

Configuration: Windows XP Firefox 2.0.0.9

10 answers

Answer 1 / 10

Best answer
The best answer is the one that has been validated by our teams.

approved by Jean-François Pillou
gbinforme Posted messages 14930 Registration date Status Contributor Last intervention 4 744
Hello

This formula should suit you. We check if A is numeric ISNUMBER(A1) and if so, we multiply, otherwise zero.
```
=IF(ISNUMBER(A1),B1*A1,0)
```
always zen
50
1. jeanchristof Posted messages 1 Status Member
  
  Hello, New to this forum, I'm sorry to revisit this old and excellent response, but it doesn't work every time. In my case, cell A1 is a number coming from a "Alea()" function... the result of my multiplication (B1*A1) is FALSE two out of three times, either PLUS or MINUS. I can't seem to find a solution. Thank you in advance to the experts for your response. JC
  
  0
2. The Engineer
  
  Hello,
  I’m returning to this old question.
  In my humble opinion, it’s simpler to use the “N” function.
  The formula becomes, in column C1:
  =N(A1)*B1
  …if I’m not mistaken.
  
  0
3. mary
  
  You're welcome :)
  
  0
4. maged
  
  thank you
  
  0
Answer 2 / 10
Nénou

Bonjour, je dois faire une facture avec Excel. Le problème de mes formules de calcul se pose lorsque je veux insérer des valeurs à mes chiffres puis de faire leur calcul. Exemple : je veux faire le produit de 10m² x 20€. Si dans la cellule A1 je mets 10 et dans la cellule B1 je mets 20, alors dans la cellule C1 j'obtiens 200 avec la formule : =A1*B1. Par contre, si je précise les valeurs m² et € respectivement dans les cellules A1 et B1, alors dans C1 j'obtiens : #VALEUR ! Comment faire pour obtenir le résultat des calculs en mettant dans les cellules A1 et B1 les valeurs 10m² et 20€ pour obtenir dans C1 = 200€ ?

Merci par avance pour votre aide capitale pour mon travail.

Nénou
20
1. Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention 6 453
  
  Hello
  For euros, there's no problem; Excel knows how to handle it, you just need to format your cells
  For square meters, it's trickier, but is it really necessary?
  Actually, if you insist, here’s a solution:
  Assuming that:
  1°) Your square meters are always indicated without spaces and without a comma between the number and m2
  2°) that you don't have more than 4 digits without a comma in the area number,
  You can:
  Your area in A1, your price per square meter in B1:
  
  In C1
  =IF(LEN(A1)=3,LEFT(A1,1),IF(LEN(A1)=4,LEFT(A1,2),IF(LEN(A1)=5,LEFT(A1,3),IF(LEN(A1)=6,LEFT(A1,4),0)))*B1
  
  With this, you should multiply the numerical value of your area by the amount per square meter in €
  
  Be careful, this formula stops at 4 digits for the area; it can increment further up to the limit of 7 "IF" statements in a row.
  In fact, the formula subtracts the total number of characters in cell A1, the number of characters specifying m2 (that is, 2 characters)
  But the big issue is that any spaces and commas are counted as characters and thus distort the result. (we can find a way to bypass the presence of the comma by fixing the number of characters to a minimum of 5, but it forces you to always enter a value such as: 20.00m2
  
  I think, after this exercise in style that serves little purpose, that it is definitely preferable to put m2 as a column title and not write it for each value entry.
  
  Or you could
  
  use an intercalary column to indicate m2. Your calculation will then be done by multiplying A1 and C1 in D1, and column B will permanently carry the m2 info;
  
  Just in case to enter this info based on the filling of A1:
  In B1:
  =IF(A1>0,"m2","")and put the price per square meter in € in column C.
  CRDLMNT
  
  --
  Truth is not possessed; it is only sought.
  
  0
2. Nados
  
  You need to create a custom format for the cells in column A, the format I created is:
  
  #.##"m²"
  
  and the format for column B exists in numbers, in your case, EURO France
  
  the result is automatically formatted in EURO
  
  the cell contents in formula mode:
  
  123 20 =A1*B1
  123 15 =A2*B2
  34 18 =A3*B3
  345 22 =A4*B4
  345 18 =A5*B5
  7 19 =A6*B6
  
  here is what you want I believe:
  
  123m² 20,00 € 2.460,00 €
  123m² 15,00 € 1.845,00 €
  34m² 18,00 € 612,00 €
  345m² 22,00 € 7.590,00 €
  345m² 18,00 € 6.210,00 €
  7m² 19,00 € 133,00 €
  
  0
3. Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention 6 453 > Nados
  
  Good evening Nados
  And well done, I didn't know that one...... (it's true that I know very few!)
  Thanks again, at least for me, if not for the requester.
  CRDLMNT
  --
  The truth is not possessed, it is only sought.
  
  0
4. Nados > Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention
  
  With pleasure!
  It was nothing much...
  
  Have a great day!
  
  Steve
  
  0
5. zebu45 > Nados
  
  Good evening,
  
  I just saw that you are knowledgeable about formulas.
  
  It may be a basic question, but I don't know how to create the following formula:
  
  - I have created an invoice,
  - I have a "quantity" column where I enter 1 - 1.5 - 2... and a "unit price" column in €
  - next to it, I obviously have a "total" column. In this column, I entered the simple formula =SUM(B12*C12) for example, but it didn't work.
  
  If I put € in the quantity, it works, and vice versa if I put normal numbers instead of € it works.
  
  So what is the formula to multiply a number with € if I can explain?
  
  0
Answer 3 / 10
anixela Posted messages 252 Registration date Status Member Last intervention 23

Good evening,

Thank you very much...you’re saving my life (well, maybe not) but at least my night!
I also knew that this thing (ISNUMBER) existed but couldn't find it in the help!!!

Thanks again
3
1. Nados
  
  Hi!
  
  You can also keep it simpler!
  
  =N(A1)*B1
  
  The N function returns the value if it's a number; if it's text, it returns 0, so by doing the calculation you get your answer!
  
  Bye!
  
  0
2. Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention 6 453 > Nados
  
  Hello...
  That's a good answer, but 4 months later, the person concerned may have left!
  BCRDLMNT
  --
  Truth is not possessed, it is only sought.
  
  0
3. nados > Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention
  
  Sorry, I hadn't seen it, I had just signed up for a search and I wanted to do the right thing!!! :)
  
  Anyway, it can be helpful for others!
  
  Bye! :)
  
  Steve
  
  0
4. JESS > nados
  
  Hello!
  
  Of course it can help others!!! The proof is that I've been searching all forums for an hour for a way to sum percentages contained in various columns, knowing that sometimes the formula giving me the percentage returns an empty "".
  And believe me, looking for the sum of non-contiguous cells that sometimes contain text... it's not easy!!
  So more than a year later, this is saving my life!! And it's so simple.
  
  Long live EXCEL
  
  A thousand thanks.
  
  Jess
  
  0
5. gbinforme Posted messages 14930 Registration date Status Contributor Last intervention 4 744 > JESS
  
  Hello
  
  <ital>and believe me, trying to sum discontinuous cells that sometimes contain text... it's no walk in the park!! /ital>
  
  And yet, it's been a long time since we could do it natively in Excel simply with the SUM function?
  --
  
  Always zen
  
  0
Answer 4 / 10
Steph

Hello and thank you all,

Indeed, some questions are a bit outdated and the answers may sometimes seem delayed, but the problems encountered by some today are or will be the problems encountered by others, today or tomorrow. Therefore, share your experiences, your tips, and bring your solutions; they will always delight the traveler who is searching for their way. Thank you.
3
Answer 5 / 10
Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention 6 453

Hello Anixela, hello gbinforme
Here is the answer to your question with gbinforme.
Just in case for the future, if you don't know it, rather than going through help:
Select the cell to argument and:
Taskbar/insertion/function:
Choose in the box what seems appropriate and follow the info to correctly enter your formula
BCRDLMNT
--

Science without conscience is but the ruin of the soul.
2
Answer 6 / 10
anixela Posted messages 252 Registration date Status Member Last intervention 23

Hello!

Thank you all...
It's surprising to receive replies even though the issue is resolved...

But still, thank you for the additional information... it can always be useful...

Have a great weekend
Anixela
2
1. nados
  
  Hi!
  Since I'm new, I didn't notice the date and if it was resolved!!!
  I apologize!
  
  Have a great day!
  
  Steve :)
  
  0
Answer 7 / 10
Eilaza

Hello,

I have a quick question:

I need to calculate cells that contain specific text and not numbers.

Example:

In my cells F8, F9, F10, F11, etc., it says P3 but sometimes these cells might contain x or c

What I need to do is to calculate (to give me a number) the total number of P3s (P3=1) that I have in my column (so F8 + F9, etc., if it says P3) and when it shows x or c, the column is worth nothing

Thank you for your help.
2
1. Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention 6 453
  
  Good evening
  
  To count in a range the number of cells that match the value you are looking for (here P3) for example in a range from F8 to F12
  =COUNTIF(F8:F12,"P3")
  
  If you want to assign a fixed value to P3 and get the count result:
  =COUNTIF(F8:F12,"P3")*Value of P3
  
  Finally, this formula can be applied simultaneously to a range of several columns, for example:
  =COUNTIF(F8:X100,"P3")
  
  CRDLMNT
  --
  Truth is not possessed, it is only sought.
  
  -1
Answer 8 / 10
atd49 Posted messages 1 Status Member 2

Hello

I would like to know if it is possible that from a cell, for example b14 which would contain the formula, I could modify cell c3 based on the text indicated in cell a1

=if(a1="text";c3="another text";"") this formula being in cell b14

Thank you for your understanding
2
Answer 9 / 10
gbinforme Posted messages 14930 Registration date Status Contributor Last intervention 4 744

Hello

The result of the formula is in the cell where it is located: if you want to change C3, you need to put the formula in C3 and not in B14.
--

Always zen
1
1. arnaud
  
  I am looking for the answer to the following exercise please:
  -2 (3x-9)-7 (2x-1)
  
  x=letter
  Urgent
  
  0
Answer 10 / 10
Ced0057

Hello,

I need a little help
I would like to display a 1 in I9 if in H9 I have "NP or C or E"

I can get a 1 if I have NP but I can't perform the OR function

=IF(COUNTIF(H9;ʺ*NP*ʺ);ʺ1ʺ;
0
1. gbinforme Posted messages 14930 Registration date Status Contributor Last intervention 4 744
  
  Bonjour,
  
  In French:
  =IF(ISERROR(SEARCH("NP",H9)),IF(ISERROR(SEARCH("C",H9)),IF(ISERROR(SEARCH("E",H9)),"",1),1),1)
  
  In English:
  =IF(ISERROR(SEARCH("NP",H9)),IF(ISERROR(SEARCH("C",H9)),IF(ISERROR(SEARCH("E",H9)),"",1),1),1)
  
  0

[Excel] Formula Cell with Number and Text

10 answers

Anomalies in my laptop usage: virus?

Doctolib plans to use your health data for its ai research

Very small left scrollbar

Missing lock icon

Caching / buffering issue on m3u stream with emby

How to disassemble the hp 6525 print head?

Unable to open xsl and xslx files

No network inside the house.

How to reset a gps on a 2010 automatic c4 picasso clocking?

Z10 that won’t boot anymore