Parse a string based on numbers/characters

[jenner85]

Hi - I am trying to parse this (Booking Point) into just the number (Cost Center) and all text after the underscores (Description)

Booking Point	Cost Center	Description
T__06158_BP_MBNA	06158	MBNA
8861_BP_GROUP SALES	8861	Group Sales

Thanks! 

[EricaR · Accepted solution]

Hi jenner85, 

Parsing with a regex should work.  See attached example. 

[echuong1 · Accepted solution]

Try using regex.

See attached for an example.

[jdunkerley79 · Accepted solution]

I would suggest a RegEx tool in Parse mode with expression:

.*?(\d+).*?_([^_]+)$

The number will be extracted by (\d+) and the rest after the _ by ([^_]+)

Sample attached

[MarqueeCrew · Accepted solution]

@jenner85 

I used this expression:

\D*?(\d+)\D.*?_(.*)

It looks for non-numbers until it finds a set of numbers and then looks for the last stuff after the last underscore.

I put the expression into an RegEx tool (configure as parse).

Cheers,

Mark