split long number

[T_Lina]

Hi, 

I need to split a long number in this way: 

All numbers have the same length and need to be split in the same way.

first number: first 4 characters

second number: following 5 characters

etc.

Can someone explain how to do it?

Thanks a lot for your help. 

[JosephSerpis]

Hi @T_Lina one approach would be to use Regex. I mocked up an approach let me know what you think? With regex you can specify a pattern to retrieve in this case my expression looks for digits where I can specify a number \d{4} will look for 4 digits in a group as denoted by () for the rest your example I just followed the pattern and at the end I used (.*) which will find everything else. Regex can only work with string fields so you may need to convert your data before hand in order to use regex.  

[jdunkerley79]

Interesting challenge. I went for:

Use a generate rows to make a sequence of 4, 5, 6,... until we have exceeded the length of the string. This is worked out by:

RowCount * (RowCount - 1) / 2 - 6 < Length([Field1])

Then a simple formula tool can extract the substring as rows:

ToNumber(
   Substring([Field1], RowCount * (RowCount - 1) / 2 - 6, [RowCount])
)

The nice thing about this approach is works regardless of the input length.

[T_Lina]

@JosephSerpis  ,

thanks for your quick response.

I replicated your solution and sadly it didn't work for me: 

Do you know why?

Thank you!

[Jonathan-Sherman]

Hi @T_Lina,

Is the value you're attempting to split in the [Object] field? At the moment you're attempting to parse the value from that field.

Kind regards,

Jonathan

[T_Lina]

@Jonathan-Sherman yes it's from the column "object".

The parts I marked appear to be double in the output columns - maybe that's why I don't get any results?

[JosephSerpis]

Hi @T_Lina you need {} not () in the regex tool  e.g. (\d{4}) not (\d(4)).