Is there any function in KNIME same as "Text to columns' in excel?

[amysg]

Hi , currently I change data format(from text to custom) using “Text to columns” in Excel .

Example:
10/3(text)
↓After “Text to columns”(delimeters "tab’)
2022/10/3(Custom)

Is there any same function in KNIME?

[lelloba]

Hello @amysg ,

maybe a Cell Splitter node is the one you are looking for.
Insert the delimiter (in your case it should be “/”), you can even remove the input column.

Have a nice day,
Raffaello

[amysg]

Thank you for your suggestion!

However, I need YYYY .
If I use cell split, hidden YYYY will disappear.

[lelloba]

If “10/03” on your Excel is in date format, on KNIME it should appear as “2022-03-10”. In this case, cell splitter will still work as required, just remember to change the separator to “-”.

If “10/03” on your Excel is simple text, there is no way KNIME can guess which year it is. What you can do is using a constant value column node and add the “year” column (see below):

Raffaello

[amysg]

Oh I see.
Actually, only after using " text to columns", YYYY shows up in excel.

so if I input excel data after modified by " text to columns", cell splitter works.
I was wondering if KNIME have same function “text to columns” as in excel.

Thank you for your reply!

[lelloba]

I made a small experiment on Excel.

If you type “10/03”, Excel will automatically recognise it as a date and guess the year (2022).
So, you type “10/03” but it is defined as date 10/03/2022, as shown in the image below. The year is hidden, but present; once you split the date, you will find it.

Excel is just guessing what year you mean. In KNIME, you can do exactly the same with the constant value column, as shown before.

Hope it helps
Raffaello

[amysg]

Before text to colums, YYYY is not exsit , but after text to colums YYYY show up.

I will modify in excel only for this point and I will import to KNIME.

Thank you for your help!