Hi,
Please refer to the below table. I want to create a lookup
6.100 Orange |
6.110 Mango |
6.120 Mango and Oranges |
6.130 Kiwi |
6.140 Doughnut with cream |
6.150 Salad |
6.160 Blueberry |
6.170 Raspberry |
6.180 Muskmelon |
6.200 Salad with Cream / Salad with Meat |
Watermelon |
Banana |
6.230 Grapes with Strawberry and Pineapple |
I only need to extract the text here so the final lookup needs to be as shown below
Orange |
Mango |
Mango and Oranges |
Kiwi |
Doughnut with cream |
Salad |
Blueberry |
Raspberry |
Muskmelon |
Salad with Cream / Salad with Meat |
Watermelon |
Banana |
Grapes with Strawberry and Pineapple |
Please help with your thoughts.
Thanks
Solved! Go to Solution.
Hi @JDong,
I would suggest going with data cleansing tool + formula tool.
Please let me know if it was helpful.
Thanks...but that does not provide me the desired output.
Please refer to the output format needed
Hi @JDong,
Apologies if I am missing something obvious but I checked in excel and the only difference is the sequence of the positions but the output is exactly the same.
Can you guide me where is the difference so I can help you out with it?
Hi @JDong,
I would go with a formula to replace the parts not needed:
Trim(ReplaceChar(REGEX_Replace([Header], '\d', ''),'.',''))
If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.
Regards,
Jonathan
Thanks..is there a free tool that generates the REGEX based on a text pattern ?
Please advise
Hi @JDong — You just need to add a Formula tool using the expression below:
Trim(REGEX_Replace([Field1], "^[\d+.,]+\s", ""))
Also, you can try "RegEx" tool from "Parse" ribbon tab for the same expression (above) with "Replace" Output Method.
"RegEx" tool is very easy to write the expressions as comes with short info about the operations you want to select.
If it resolves your query Please mark it "Solved" or "Solved" with a Like. This will help other users find the same answer/resolution. Thank you.
Thanks all of the above helps !
One question, there is a text
6.822-823 Watermelon/ -apple
Output should be
Watermelon/ -apple
This is still not getting fixed using the REGEX. Any help here ?