Split a text based on character position in string

pushkardps

Hello,

Can anyone suggest the best way to do the following:

9177454426 into columns 917 col 1, 745 col 2, 4426 col 3.

Basically, I looking to cut a string and put the data into columns.

I think I can use Left() function and Right() function for col 1 and col 3 but how to select data for col 2?

Thank you for looking into this.

Preparation

Accepted answers

MarqueeCrew

Two ideas come to mind, but I'm fond of :

Regex_Replace([string],"(\d{3})(\d{3})(\d{4})",'$1,$2,$3')

If your number isn't a string, you'll want to change it with ToString().

You'll likely use a text to columns tool next. There are other ways as well. Do try the substring to get that middle set too. There are lots of ways to skin this challenge.

Cheers,

Mark

All comments

MarqueeCrew

pushkardps

Aah, beautiful. It worked perfectly.

That makes me think I need to do a deep dive into learning Regex.

Thanks a ton.

P.S. - You were right about using substring to solve it as well. I will admit my first instinct will be always use substring function but I am pretty sure Regex is a better way to handle it.

Substring([P1],0,3) + '.' + Substring([P1],3,3) + '.' + Substring([P1],6,4)

willem

Hi Mark,

Wondering if you can assist. I used your advice a starting point to parse a field based on character position. It worked perfectly except for on record. I've copied this record and one good one into the text input tool to demonstrate (and obscured the potentially sensitive data).

The formula is Regex_Replace([Field1],'(.{13})(.{6})(.{30})(.{15})(.{16})(.{13})(\w|\W{30})(\w|\W{30})','$1,$2,$3,$4,$5,$6,$7,$8')

The bad record returns 15 digits for the first group instead of 13 as I expect it to. Workflow attached.

Why could this be?

Thanks in advance,

Willem

Regex parse sample.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703