Skip to main content

Grab quantities out of text

  • January 12, 2023
  • 4 replies
  • 56 views
J
Forum|alt.badge.img+3

Hi there, 

I have some recipes here that appear in one column like: 

Salmon (F) 280g / 560g

Lemon 0.5 / 1

Potatoes 2 / 4

Tomato 2 / 4

Capers 20g / 40g

 

Etc. 

I would like to have another column get the qty on the left of the / and another to get the qty on the right of the /

so column 1 = 

280g

0.5

2

2

20g

etc. 

 

I am struggling to figure this one out x 

 

    4 replies

    Dimitris_Goudis
    Forum|alt.badge.img+20

    Hey @jm22 , 

    nice challenging task!! 

    Below in screenshot are the columns you need to create and the formulas you have to add in each of them. I added formulas in separated fields because in nested formulas it would be a little bit strange to get the logic. 

    * find slash -> formula to find the position of your slash

    FIND("/",{Field 1 text})

    * left of slash -> formula to get everything that exists left of slash excluding the slash and the space before it

    LEFT({Field 1 text},{find slash}-2)

    * right on left of slash -> formula to get what exists 5 positions from right to left on the {left of slash} formula 

    RIGHT({left of slash},5)

    * find space on {right of slash} -> to recognize if there is any space in the return of the formula {right on left of slash}, if this is 1 means that the result is what you are looking for otherwise you need one cleaning step more.

    FIND(" ",{right on left of slash})

    * final_result -> the if formula that runs the conditional logic to perform this extra cleaning step

    IF( {find space on {right of slash\}}=1,{right on left of slash}, RIGHT({right on left of slash},LEN({right on left of slash})-{find space on {right of slash\}}) )

    For any further question please feel free to post here 🙂 

    Yours sincerely, 
    Dimitris Goudis

     

     

    1 Attachments
      J
      Forum|alt.badge.img+3
      • jm22
      • Author
      • New Participant
      • January 12, 2023

      that worked!!! holy smokes that is so COOL!!! I have tried to get everything on the right hand side by reversing everything but can't get it to work. can you help me again? 

      Salmon (F) 280g / 560g

      Lemon 0.5 / 1

      Potatoes 2 / 4

      Tomato 2 / 4

      Capers 20g / 40g

      to Get:

      560g

      1

      4

      4

      40g 

       

      ??

       

      J
      Forum|alt.badge.img+3
      • jm22
      • Author
      • New Participant
      • January 12, 2023

      I got it!! just needed one extra column to get rid of the slash 🙂 

      wow this is a game changer, I cannot believe this has worked! 

      Dimitris_Goudis
      Forum|alt.badge.img+20

      I got it!! just needed one extra column to get rid of the slash 🙂 

      wow this is a game changer, I cannot believe this has worked! 


      Hey @jm22,

      I am very happy for helping you 🙂 Did you find the solution for the right part? 

      Thanks

      Terms & ConditionsAccessibility statement