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

[pk1]

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 solution]

Try the attached.

First regex without changing the -:

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

Then correct the -

[MSalvage · Accepted solution]

@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

[pk1]

Thanks for your help, Expression worked. But in input (CCP-14, CCP-16) is a single row, So first i have to split it into 2 rows and then apply Regex formula