find string with certain text and number after that string

[Nmassarone]

Hello I'm looking for help with a table i have where I'm trying to extract a certain string within a longer string in a cell.

i posted below an example i have a long string name and inside that string I'm trying to extract the IRK or BRK within that string along with the numbers after it. The only thing is the IRK or BRK number may be in various points along the string and some are not always separated by themselves they might have a - or be connected to a word. Also the numbers after the IRK or BRK are random and are not always the same.

i posted below an example of the table and the ideal column i would want outputted

The Record_Num is the ideal column I would like extracted from the string to be displayed wasnt sure if there was a certain regex that would help.

i tried searching all over to see if there was a solution for this but couldn't find anything if someone is able to help with this it would be greatly appreciated.

This is the table and the REG_NM is where the IRK or BRK number is and the Record_Num column is what i would ideally like displayed extracting that IRK or BRK number from the REG_NM column.

ACCT_STUS_DESC	REG_NM	Record_Num
OPEN	RECORDKEEPERDIRECT -BRK54321 COMPASSIONATE FRIENDS PLAN SHANE	BRK54321
OPEN	RECORDKEEPERDIRECT -BRK54321 COMPASSIONATE FRIENDS PLAN JOHN	BRK54321
CLOSED	ROSEVILLE  401K PSP ACCOUNT    -BRK12345 ROSEVILLE	BRK12345
OPEN	RECORDKEEPER DIRECT - IRK9876 JOHN SMITH DDS MS LLC 401K KIM	IRK9876
CLOSED	CLEAR ZONE INC FORFEITURE - IRK2345 CLEAR ZONE INC	IRK2345
CLOSED	RECORDKEEPERDIRECT - IRK3456 ANGEL CARE JAS	IRK3456
CLOSED	RECORDKEEPERDIRECT - IRK3456 ANGEL CARE MARV	IRK3456
CLOSED	BRO RECORDKEEPING - BRK6789 SUMMIT SCHOOL JASON	BRK6789
OPEN	BRO RECORDKEEPING - BRK6789 SUMMIT SCHOOL CHARLES	BRK6789
CLOSED	CYNETSIC 401K PLAN EXPENSE ACCOUNT    - IRK4567 CYNETSIC 401K PLAN	IRK4567
CLOSED	VG GROUP PLAN ACCOUNT    - IRK8765 VG GROUP PLAN	IRK8765
OPEN	RECORDKEEPERDIRECT -IRK65432 AMERICAN COMM 403B KENIA	IRK65432
CLOSED	SMITH & ASSOC SC 401K FORFEITURE ACCOUNT - BRK76543 SMITH & ASSOCIATES SC	BRK76543
OPEN	RECORDKEEPERDIRECT -IRK1928 AMERICAN COMM 403B JOHN	IRK1928
OPEN	BRO RECORDKEEPING - BRK6789SUMMIT SCHOOL CHRISTINA	BRK6789

Any Help would be greatly appreciated.

thank you in advance.

[MilindG]

I have used Regex to solve this. Check out the file :)

[DataNath · Accepted solution]

Here's how I'd do this with RegEx, as long as IRK/BRK are the only two starting points for the target.

([IRK|BRK]+\d+)

[Nmassarone]

Awesome Thank you so much @DataNath  and @MilindG  both worked perfect appreciate the help on this. thank you.