Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 12, 2023 01:26 AM
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
Jan 12, 2023 02:29 AM
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
Jan 12, 2023 04:54 AM
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
??
Jan 12, 2023 05:00 AM
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!
Jan 12, 2023 05:04 AM