looking for elegant way to split AND fill-in strings

[mikael_svalgaard]

Hi all,

I have some string data where this '17081234_235_236' means '17081234', '17081235', '17081236'. More generally, I need to go from this kind of input:

to this output:

Note that the length of the '_' extensions can vary, and if the length equals or surpasses that of the original string then the operation is merely split-to-lines, i.e. no in-filling (lines 4 and 5 of the input example).

Any ideas for an elegant way to do this?   -thanks, Mikael

[Luke_C]

Hi @mikael_svalgaard 

You can use a formula for this. The findstring will get the position of the first underscore, then you can use that in the left function to take everything left of that position.

left([FullPath],findstring([FullPath],'_'))

[atcodedog05 · Accepted solution]

Hi @mikael_svalgaard 

Here is how you can do it.

Workflow:

Hope this helps : )

[Luke_C]

@atcodedog05 you read all of the instructions! I read through way too fast. Clever solution.

[atcodedog05]

Hi @Luke_C 

Honestly i do that a couple of times too 😅

[mikael_svalgaard]

thanks @atcodedog05 - you are a wizard! I marked it as a solution. Only caveat is that the last line of my test data didn't work, but I don't think that my real data has cases like this where the later string(s) are longer than the first.  cheers, Mikael

[atcodedog05]

Happy to help : ) @mikael_svalgaard 

Cheers and have a nice day!