Split a string field into two fields using RegEx / Text To Columns ?

Question

Hi All,

I want to split a column/field into 2 fields, First i removed " -" from all fields and used RegEx Expression: " (\<\w+\>)(.)(\d+) "  &  "(.*)\s(.*?\>)" in RegEx , both did not work for me.

Input Field

SERA-14

MERA-SERA-15A

PET-YB-64B

PET-XB-64B

RB&L-13

CCP-14, CCP-16

Output Field 1         Output Field 2

SERA                           14

MERA SERA               15A

PET YB                       64B

PET XB                       64B

RB&L                           13

CCP                             14

CCP                             16

Can anyone please help me out to solve this issue

Thanks in advance

jdunkerley79 · Accepted Answer

Try the attached.

First regex without changing the -:

(.*)-([^-]+)

Then correct the -

MSalvage · Accepted Answer

@pk1,

@jdunkerley79's solution should work unless that last data point really does contain "CCP-14, CCP-16". If so you will want to use the text to columns tool first, set to split to rows with ", "(comma space) as the delimiter and selecting skip empty fields.

Best,

MSalvage