Add a character after every nth charachter in a string.

Hello Community,

Looking for some help to allow me to finish off a project that I am working on. The concept is fairly straight forward but I am having issues resolving. Basically, I have records where I want to add a delimiter after every 4th value so I can use text to columns to split our the 4 character ID fields I need to isolate. I feel like the solution is a regex expression of formula of some kind.

Once I can successfully split out the values using text to columns, is there a way to pivot the data from each row into one column with all the values?

Any help would be greatly appreciated!

Thank you,

Dan

Capture.JPG

Common Use Cases

Preparation

Accepted answers

philipmannering

Hi dgsigl2

Assuming I've understood you correctly (which is a massive assumption), this might help you (see below)

Using the regex tool in tokenize mode it splits every 4 character into a separate row. The regex expression is simply four periods/full stops: ....

Use recordID beforehand if you want to keep track of which row they came from.

4_characters.yxmd

All comments

philipmannering

Hi dgsigl2

Assuming I've understood you correctly (which is a massive assumption), this might help you (see below)

Using the regex tool in tokenize mode it splits every 4 character into a separate row. The regex expression is simply four periods/full stops: ....

Use recordID beforehand if you want to keep track of which row they came from.

4_characters.yxmd

MarqueeCrew

Hello @dgsigl2,

No picture here. I have a working solution, but I'm going to describe it to you.

SELECT
- You need to make sure that the input data length is long enough to support the addition of dots into the values.
RegEx
- Field to Parse: this is your input field
- Regular Expression:
```
(.{1,4})
```
  A group of characters with a length of 1 to 4. This will cycle through the 4 digit values. If you end up with a last set of characters that is less than 4 it will put them into that final group. If you only allow for 4 as the length, it would DROP the last values.
Formula
- Remove the final . from the end of the field
```
Trim([Input], ".")
```

Cheers,

Mark

LordNeilLord

Ignore me

MarqueeCrew

@dgsigl2,

While I like the .... aspect of @PhilipMannering's solution and appreciate @LordNeilLord providing you with a more complete solution, I do want to point out that the solution provided REQUIRES that the sets all be 4 characters in length. If the input is ABCDEFG, the result will drop the last characters because there are not 4 characters in that set/group.

I prefer my solution because it does not require a shift to ROWS and then a shift back to a single field. If you want to use that solution, please modify the .... to .{1,4}

Cheers,

Mark

ponraj

Here is the sample workflow for your case... Regex \w{4} with output method tokenize with properties of split to rows, recording id, summarize and join tools will get you the desired output.

Add a character after every nth charachter in a string - solution.yxzp

philipmannering

@MarqueeCrew Good point on using the expression .{1,4} if you're codes aren't a length that is a multiple of 4. Hadn't thought of that.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703