Adjust regex to account for second alpha-numeric designator separating it from values

Regex question. I need to separate Field1 into 2 outputs. The regex expression below parses Field1 correctly 99%. The pattern is a 1-3 digit number \d{1,3}, a space \s, a designator that contains letters and numbers, a space, and then a series of 1 to 3 values that can be either positive or negative.

(^\d{1,3}\s.*?)\s(.*$)

The problem can be seen in the first row below. The row that starts 165... contains a second designator that contains both numbers and letters before the 1 to 3 positive or negative values. In this example, the expression above fails to catch this second designator. How can I fix it?

Field1	WRONG 1st Group (First Row is wrong)	DESIRED 1st Group
165 D–35 C2D2 785336 409524 -375812	165 D-35	165 D-35 C2D2
1 D–35 -4567	1 D-35	1 D-35
20 Q–9 29409 -29409	20 Q-9	20 Q-9
59 E–4 58803 44140 -14663	59 E-4	59 E-4

Regex

Parse

Accepted answers

atcodedog05

Hi @hellyars

Here is a workflow for the task.

Output:

Workflow:

Formula:

TrimRight(REGEX_Replace([Field1], "\s-?\d+", "|$1"),"|")

Hope this helps 🙂

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

regex.yxmd

philipmannering

I think this works in the Regex Tool set to Parse,

(.*\u\S*) (.*)

All comments

hellyars

I came up with a brute force solution using the following expression.

(^\d{1,3}\s.*?\s\w*?)(\d{2,8}.*$)|(^\d{1,3}\s.*?\s.*?)\-\d{1,8}

But, it creates additional output fields, when I only want two.

atcodedog05

Hi @hellyars

Here is a workflow for the task.

Output:

Workflow:

Formula:

TrimRight(REGEX_Replace([Field1], "\s-?\d+", "|$1"),"|")

Hope this helps 🙂

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

regex.yxmd

philipmannering

I think this works in the Regex Tool set to Parse,

(.*\u\S*) (.*)

hellyars

@PHIS Thank you. This also works and allows me to parse the title/ designator from the dollar values.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703