Split a column without delimiters

[shmakh]

Hi,
I am trying to merely split a column of 20-digit balance sheet accounts as follows: take the first 5 digits and drop the rest. With those accounts starting from ‘7’, I would want to do the same but not with the first 5 digits but starting from the 14th one. Could you help me accomplish that?
P.S. There are no delimiters inside the account number/cells containing the numbers. Just the 20-digit numbers.

[elsamuel]

Welcome to the forum, @shmakh.

For this, you can first split the data into 2 groups for your two different cases.
Then you can use Cell splitter By Position nodes to split at whatever positions you want…

[ArjenEX]

Do you actually want to split or extract? Because in your description you mention to drop the rest .

If you want to drop the rest, you can resolve to an one node solution by using the Column Expression with

if (indexOf(column("column1"),"7" ) == 0) {
    substr(column("column1"),14 , 6)
} else {
    left(column("column1"),5)
}

It checks if the number starts with a 7, if so take the digits from position 14 and onwards.
If not, take the first 5 digits.

[shmakh]

To be honest, it is all part of a regular excel sumifs() operation that I cant figure out. I’ve made the table look like this:

where the right-most column is unique accounts derived from the column next to it. Now I need to find a sum of balances for each of the unique accounts.

[ArjenEX]

Oke, if that is the desired output then the final step of finding the sum of balances for each account can be done with a GroupBy node.

Assume this small example:

In the GroupBy node, use the account number as Group Column.

Next, go to the Manual Aggregation sheet and add the balance column, and use Sum as means of aggredation.

Final result:

In general, try to be as complete as possible in your questioning. Include current input, expected output, screenshots, data sets, workflows, etc. as much as possible in the beginning. The more you provide, the more accurate the help will be

[shmakh]

Thanks for your help!
Could you please look down below?

[shmakh]

Thanks! Dearly appreciated