This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have a data contained within a table that consists of a | (pipe) e.g. CAM Supervisor|CAM Users|CAM Group Supervisor
I need to replace the | with a , (comma).
The current formula I'm using: REGEX_Replace([Role], "|", ",")
Unfortunately the results I receive is as follows: ,C,A,M, ,S,u,p,e,r,v,i,s,o,r,|,C,A,M, ,U,s,e,r,s,|,C,A,M, ,G,r,o,u,p, ,S,u,p,e,r,v,i,s,o,r, which is incorrect
Can someone advise as to what I'm doing wrong, or the correct formula to use.
Many thanks.
Just use this formula: Replace([Role], '|', ",")
-Vijay
Hi @Vijay_M
The pipe character "|" is a reserved character in REGEX meaning Logical OR. When you use this without escaping it, it matches every character. To match the literal "|" character, add a backslash in front of it, like this
REGEX_Replace([Role], "\|", ",")
Then your results will be
CAM Supervisor,CAM Users,CAM Group Supervisor
Dan
This solved the problem, many thanks
I had not appreciated that the pipe was reserved character and needed to prefix the code with a backslash prior to the pipe e.g. "\|"