View original (French)

Formula with text inside

Solved
Severine33130 Posted messages 114 Registration date   Status Member Last intervention   -  
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   -

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 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     

    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 Posted messages 114 Registration date   Status Member Last intervention   2
       

      Hello Mike,

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

      But thank you anyway.

      Séverine.

      0
  2. Pierr10 Posted messages 13848 Registration date   Status Moderator Last intervention   5 850
     

    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 Posted messages 114 Registration date   Status Member Last intervention   2
       

      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
      1. Pierr10 Posted messages 13848 Registration date   Status Moderator Last intervention   5 850 > Severine33130 Posted messages 114 Registration date   Status Member Last intervention  
         

        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 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     

    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 Posted messages 25137 Registration date   Status Member Last intervention   4 167
       

      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 Posted messages 114 Registration date   Status Member Last intervention   2
       

      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
    3. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147 > Severine33130 Posted messages 114 Registration date   Status Member Last intervention  
       

      Have you looked at and tested my post No. 5?

      0
    4. eugene > Severine33130 Posted messages 114 Registration date   Status Member Last intervention  
       

      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 Posted messages 25137 Registration date   Status Member Last intervention   4 167 > 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 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     

    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 Posted messages 114 Registration date   Status Member Last intervention   2
       

      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 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     

    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 Posted messages 114 Registration date   Status Member Last intervention   2
       

      Here is the link to my folder

      https://we.tl/t-ZR8DmC2BdB

      0
  7. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     

    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 Posted messages 114 Registration date   Status Member Last intervention   2
       

      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 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     

    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
    1. brucine Posted messages 25137 Registration date   Status Member Last intervention   4 167
       

      Hello,

      Likely reference to this discussion and where it works:

      https://forums.commentcamarche.net/forum/affich-38209065-date-qui-ne-fonctionne-pas#dernier

      0
      1. brucine Posted messages 25137 Registration date   Status Member Last intervention   4 167 > brucine Posted messages 25137 Registration date   Status Member Last intervention  
         

        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
    2. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
       

      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
      1. Severine33130 Posted messages 114 Registration date   Status Member Last intervention   2 > Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention  
         

        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 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     

    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