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
