split long number
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Data Investigation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
thanks for your quick response.
I replicated your solution and sadly it didn't work for me:
Do you know why?
Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @T_Lina you need {} not () in the regex tool e.g. (\d{4}) not (\d(4)).
