Can i use regex for this?

I have data that sometimes has a commas after 10 characters and some that don't. what i want to do is consistently have a comma after each 10 digits, unless there are only 10 digits. So basically i want the following:

45028357424502938101,4502938115

4502835734

4502755826450290882145029381104502938415

4502755826,4502908821

To look more like this:

4502835742,4502938101,4502938115

4502835734

4502755826,4502908821,4502938110,4502938415

4502755826,4502908821

I'm hoping there is a regex expression to help with this.

Thanks!

Datasets

Tips and Tricks

Transformation

Accepted answers

atcodedog05

Hi @krishnagandhi

Here is way using Regex and single formula

TrimRight(Replace(REGEX_Replace([Text], "(\d{10})","$1,"), ",,", ","),",")

Output:

Workflow:

Select tool is used to increase datatype size to avoid truncation.

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

num.yxmd

All comments

atcodedog05

Hi @krishnagandhi

Honestly i dont know whether this can be solved only with regex

Here is a workflow for the task.

Output:

Workflow:

Hope this helps 🙂 I look for a regex way

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

num.yxmd

atcodedog05

Hi @krishnagandhi

Here is way using Regex and single formula

TrimRight(Replace(REGEX_Replace([Text], "(\d{10})","$1,"), ",,", ","),",")

Output:

Workflow:

Select tool is used to increase datatype size to avoid truncation.

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

num.yxmd

jdunkerley79

My default answer to Can I use regex for this? is yes.

So here you go:

(\d{10}(?=.))

will match blocks of 10 as long as a character after it

Works fine in a REGEX in substitute mode with $1, as the replacement text

Sample attached.

Regex blocks of 10.yxmd

atcodedog05

Hi @jdunkerley79

There are duplicate commas in your output can something be done about it.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703