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.

[MarqueeCrew · Accepted solution]

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

[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

[willem]

Actually, never mind (if you were going to!) - I think I've got it. Still not sure why the first marked group was the one to come out wrong, but it seems I counted total characters wrong in the initial field and that must be what did. I've fixed the formula to:

Regex_Replace([Field_1],'(.{13})(.{6})(.{30})(.{15})(.{16})(.{13})(.{30})(.+)','$1,$2,$3,$4,$5,$6,$7,$8')

and that seems to have done the trick.