Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Grab quantities out of text

1785 0
cancel
Showing results for 
Search instead for 
Did you mean: 
jm22
5 - Automation Enthusiast
5 - Automation Enthusiast

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 4

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

 

 

jm22
5 - Automation Enthusiast
5 - Automation Enthusiast

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 

 

??

 

jm22
5 - Automation Enthusiast
5 - Automation Enthusiast

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