# Grab quantities out of text

1304 4
cancel
Showing results for
Search instead for
Did you mean:
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
9 - Sun

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

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

??

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