Split column by last 4 letters/characters

[knimedt]

Hi everyone,

I have a column with Text/letters and the last 4 characters are the year.
Now, I want to split this column into 2 to get, e.g. First colume: Sales, second column: 2021.
I tried Regex Split with this expression (.[A-Za-z])(.[0-9].* but it only works if the 5th character from the right is not a number. Unfortunately, I have many cases like that.
Cell Splitter by position only works from the left.

Thank you so much for your help.

[badger101]

Hi @knimedt There usually are many ways to work around cases like this depending on the data formats. You’ve mentioned you considered using the ‘Cell Splitter By Position’ node, but the issue was that it only works from the left, which is true.

Here’s what you can do in your workflow:

String manipulation > Cell Splitter > String Manipulation

The cell splitter should be configured (based on your description above) as such:

Using a space character as the delimiter.

And both String Manipulation nodes both should use the reverse function:

The first reverse function is self-explanatory, and the second time is to revert back to original (unreversed) strings.

You might also explore the Column Expressions (as I’m sure someone will suggest below), but scripts aren’t my forte.

[mlauber71]

@knimedt you could determine the length of the string and then extract the last portion …

substr(column("column1"),length(column("column1"))-4 , 4)

extract the last 4 characters from a string – mlauber71

[knimedt]

Thanks! Sounds complicated to me.

[knimedt]

Thank you so much! Works perfectly!

[iCFO]

It would be great if we just had a simple “split by position” setting built into the Cell Splitter Node. I feel like this is one of the most common forum questions. Most users come across this requirement early in their KNIME learning process, so it would be nice if there was a simple solution that didn’t require a solid formula / Regex knowledge base. We don’t always have delimitations to work with…

A simple split by position setting with a left / right selector and position number would be helpful to new users and nice option in general.