View original (French)

Formula with text inside

Solved
Severine33130 -  

Hello,

Is it possible to get a correct result by entering the following formula: =B20*B22, knowing that:

B20 : 5320 m2

B22 : 11 €/m2

and this without adding a column (my table does not allow it)

My result displays as follows => #VALUE!

Thank you all.

Have a nice day.

8 answers

  1.  Mike-31

    Hello,

    If your data is formatted correctly, you can simply do =B20*B22

    however, if m² and €/m2 have been added manually, your data is recognized as text, so you need to extract each numeric value

    =LEFT(B20;FIND(" ";B20)-1)*1

    and

    =LEFT(B22;FIND(" ";B22)-1)*1

    which results in the completed formula

    =(LEFT(B20;FIND(" ";B20)-1)*1)*(LEFT(B22;FIND(" ";B22)-1)*1)

    See you
    Mike-31

    I am responsible for what I say, not for what you understand...

    0
    1. Severine33130 Author

      Hello Mike,

      My result isn't good: I got 55 instead of 5320.

      But thank you anyway.

      Séverine.

      0
  2. Pierr10 Moderator

    Hello,

    Another way to proceed is to enter the values 5320 and 11 without units and then format the cells to add a unit.

    Enter 5320 then right-click on the cell and choose Format Cells.

    Then select Custom and type 0" m2" in the Type field. Confirm.

    Do the same for the cell containing the price per m². In Type, you will write 0" €/m2"

    You can now use your formula to perform the calculation.

    For the cell that contains the result, you will apply formatting using the currency format and the result will be in €.

    What one conceives well is clearly expressed,
    And the words to say it come easily.
    (Boileau)

    0
    1. Severine33130 Author

      Hello,

      It's not working.

      Regarding the price/m2, here is the message: Microsoft Excel cannot use the number format you entered.......

      As for the m2 cell, it does not display m2 but just 5320.

      0
    2. Pierr10 Moderator
      @Severine33130

      Before being formatted, the cell contains only a number (no unit).

      When formatting, be careful with spaces in Type. Do not add any!

      0
  3. Mike-31

    Re,

    how is it not working! To get 55, there is a space after the 5, which makes it 5 3230 so that’s 5*11=55

    either you have spaces lingering in your entries or?

    retrieve my example from this link and compare differently formatted values in j3 and L3

    or with the values you provided in your request

    https://transfert.free.fr/QL5jMmn

    or to handle an indefinite number of spaces

    =(LEFT(SUBSTITUTE(B20;" ";"");LEN(SUBSTITUTE(B20;" ";""))-2)*1)*(LEFT(SUBSTITUTE(B22;" ";"");LEN(SUBSTITUTE(B22;" ";""))-4)*1)

    Later+
    Mike-31

    I am responsible for what I say, not for what you understand...

    0
    1. brucine

      Hello,

      it indeed works perfectly on my end, where I suppose that the destination cell must be in currency format but I am not following the comment in <3>: the expected result is not 5320 but 58520.

      0
    2. Severine33130 Author

      So !!!!

      I managed to copy your formatting for the m2 and €/m2, but the formula =(LEFT(B23,FIND(" ",B23)-1)*1)*(LEFT(B25,FIND(" ",B25)-1)*1) gives me #VALUE even though I copied your formula and changed the cell because I added rows.

      0
    4. eugene
      @Severine33130

      Hello Severine,
      In Excel, mixing text and numbers in the same cell is rarely a good idea. But it's up to you...
      Best regards.

      0
    5. brucine
      @eugene

      Hello,

      It's not clear why.

      It can be more or less complex when there are strings to extract depending on their length or position, but there are a multitude of trivial examples.

      0
  4. Mike-31

    Hi again,

    To address your issue, it would be wise to have your file on hand to see what's wrong.

    With Excel, nothing is impossible.

    See you later,
    Mike-31

    I am responsible for what I say, not for what you understand...

    0
    1. Severine33130 Author

      Hi,

      Where I work, I don't have the rights, and I can't find the "attached" site to send files anymore.

      0
  6. Mike-31

    Hi,

    to send a file you can use

    https://wetransfer.com/

    then do not send a file with personal data, in two columns simply enter several types of data that are problematic and copy the link in a reply.

    See you later,
    Mike-31

    I am responsible for what I say, not for what you understand...

    0
    1. Severine33130 Author

      Here is the link to my folder

      https://we.tl/t-ZR8DmC2BdB

      0
  7. Mike-31

    Hi,

    Your data is correctly used, in the initial BdD tab cell B23 you entered a numeric value of 5320 with a custom format Standard" m²"

    in cell B25 the entered data 11 is also formatted Standard"  €/m²"

    In C25 the formula =B23*B25 returns 58,520.00 € because it is formatted in monetary format with a thousand separator.

    I don't see where the problem is, everything is correct

    See you later
    Mike-31

    I am responsible for what I say, not for what you understand...

    0
    1. Severine33130 Author

      Thank you.

      I just reformatted my files and it's working.

      However, if it's not too much to ask, could you also check the dates for REP1, REP2, and REP3 as I should normally have:

      REP 1 with the date from the initial BdD sheet and that's not working.

      Thanks in advance.

      0
  8. Mike-31

    Hi,

    In cell B11 you have a date 22/05/2019 and in B12 you have 13/01/2022.

    What do you want to do with these dates and in which cell, and what result do you expect from these dates?

    See you soon,
    Mike-31

    I am responsible for what I say, not for what you understand...

    0
    2. Mike-31

      I found your error and what you wanted to do, in the Fiche OP tab cell C60 you did not correctly address your target cell

      =IF(ISBLANK(B11),"REP 1","REP 1 of "&TEXT('BdD initiale'!$B$11,"dd/mm/yyyy"))

      try it like this and the same for cells C61 and C62

      =IF(ISBLANK('BdD initiale'!B11),"REP 1","REP 1 of "&TEXT('BdD initiale'!$B$11,"dd/mm/yyyy"))

      0
    3. brucine
      @brucine

      The formula has been correctly modified in C122, but not in C140 and C141 (date formatting) and in C60 to C62 (it should refer to cell B11 of the initial database).

      0
    4. Severine33130 Author
      @Mike-31

      Fabulous!!!! I love it when everything goes smoothly.

      Thank you for your patience.

      I'm so bad with formulas that I should stop making tables like this..... but it's so convenient once everything is set.

      Again, a big thank you.

      Have a great weekend!

      0
  9. Mike-31

    You can merge the cells Fiche OP from C60 to J60 with left alignment, and do the same for REP 2 and 3 on lines 61 and 62

    Change the status of the discussion to resolved

    Have a good weekend

    See you later
    Mike-31

    I am responsible for what I say, not for what you understand...

    0