Hi All,
I got a bunch text with characters and words I want to clean up , I have come up with a Regex_Replace formula, it seems to only do part of what I want ( it doesn't remove anything after specific word) ...could someone help trouble shoot this formula? Thanks
find below item and replace with blanks:
1. remove all after period (.)
2. remove all after semicolon (:)
3 remove all after certain word (Respect) or (Behalf)
formula below:
trim(REGEX_Replace(
(if Contains([Line Items Description], ".")
then Left([Line Items Description], FindString([Line Items Description], "."))
elseif Contains([Line Items Description],"-")
then Left([Line Items Description], FindString([Line Items Description], "-"))
elseif Contains([Line Items Description]," : ")
then Left([Line Items Description], FindString([Line Items Description], " : "))
elseif Contains([Line Items Description],"RESPECT")
then Left([Line Items Description], FindString([Line Items Description], "RESPECT"))
elseif Contains([Line Items Description],"BEHALF")
then Left([Line Items Description], FindString([Line Items Description], "BEHALF"))
else [Line Items Description]
endif),"(.*)\-(.*)", "$1"))
Sample below:
| FEE FOR PROFESSIONAL SERVICES RENDERED IN RESPECT OF AN APPLICATION FOR SHORT STAY WORK VISA . FEE APPROVED BY xxxxx ON -OCT-. AUD$. +GST CONVERTED TO USD - OUT OF SCOPE |
| GOVERNMENT FEES PAID ON YOUR BEHALF. AUD . |
| FOR PROFESSIONAL SERVICES RENDERED WITH RESPECT TO THE COORDINATION OF IMMIGRATION REQUIREMENTS - WORK PERMIT CANCELLATION - USD |
| FOR PROFESSIONAL SERVICES RENDERED WITH RESPECT TO THE ASSESSMENT FOR WORK PERMIT USD, |
| GOVERNMENT FEES PAID ON YOUR BEHALF - RESIDENCE PERMIT . CNY |
