Column Rename (Regex)

Hi, I have a bunch of column names that I need to change/remove the same portion from each column name. I know that Column Rename (Regex) is best but I can’t figure out what to put since I need to remove parentheses also.

Example Column Names…
2020+Mean(Total # Sold-Tos)
2020+Mean(Total # Sold-To Clusters)
2020+Mean(Web # Sold-Tos)
2020+Mean(Web # Sold-To Clusters)
2020+Mean(Offline # Sold-Tos)
2020+Mean(Offline # Sold-To Clusters)
2020+Mean(S_Web # Sold-Tos)
2020+Mean(S_Web # Sold-To Clusters)
2020+Mean(M_Web # Sold-Tos)
2020+Mean(M_Web # Sold-To Clusters)
2020+Mean(S_Offline # Sold-Tos)
2020+Mean(S_Offline # Sold-To Clusters)
2020+Mean(M_Offline # Sold-Tos)
2020+Mean(M_Offline # Sold-To Clusters)

I need to remove the +Mean( and the closing )
example would be 2020 Total # Sold-Tos

Thank you in advance!

You may need to rename the columns twice. Once to remove the “+Mean(” and once to remove the trailing “)”.

Regex 1 could be \+Mean\( and regex 2 could be \).

Try the expression (\+Mean\()|\)

.?(Mean).?())
should give you the mean and the last paranthesis as match as groups
br

Hi @ahortonmilsig , based on what you are looking for, you have to do in 2 renames.

If you just wanted to remove +Mean( and the closing ) as you first stated, then yes it can be done with 1 rename, since you would be replacing both with an empty string.

However, based on your example, that is not what you are asking. Based on what you said:

So, it’s not just about removing +Mean(, but replace it with a whitespace.

There are actually 2 different rules there:

  1. Replace +Mean( by a whitespace
  2. Remove ) or replace by an empty string

If you can’t figure out what expression to use, there is an alternative way. You can extract the headers, and do simple replace on the headers without any regex, and then replace with the new headers.

Something like this will do the job:
image

Original table headers:

Table headers after processing:

The manipulation is done in 1 statement:

replace(replace($Column Header$
  , "+Mean(", " ")
  , ")", "")

Here’s the workflow: Column rename alternative.knwf (11.3 KB)

2 Likes

Hi @ahortonmilsig

join( substr($column1$, 0, 4), " " , regexReplace($column1$, "\\(([^()]*)\\)|.", "$1") )

BR

Hello @ahortonmilsig,

You are right. Don’t complicate it.

ColRenameRegex

Br,
Ivan

2 Likes

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.