Regex expression to split into rows based on entire word???

Question

Hello,

I have the entire dataset in one cell in excel. I'm trying to split it into different rows based on a specific word. Each new entry starts with the work 'Provider'. So i'm basically looking for a formula where when it reaches the word Provider it moves the entire text to the next row.

I'm assuming regex has something to do this instantly, but i'm not well versed with regex, but wouldn't mind via formula tool as well

Dataset - 
Provider ID #: XXXX Office #: 111 name - dr - address, PA 999 (111) 000-0000 Provider ID #: YYYY Office #: 222 name - GP dr - address, NJ 888 (222) 111-1111

Output required -

Provider ID #: XXXX Office #: 111 name - dr - address, PA 999 (111) 000-0000

Provider ID #: YYYY Office #: 222 name - GP dr - address, NJ 888 (222) 111-1111

Once I have this, i plan to then split the provider, office, name, address, phone using text to column on delimiters

binu_acs · Answer

@Rosh_2788 @The Regex expression looking for a pattern which starting with the text “Provider ID #:.” then any characters followed by this text then ending with the pattern d{3}\s$\d{3}$\s\d{3}\-\d+,

Rosh_2788 · Answer

@binu_acs thank you, this works for me! for my knowledge, could you please explain the expression?

@apathetichell thanks for looking into this, however, i wasn't able to follow, my dataset i already grouped/concatenated in one cell

binu_acs · Answer

@Rosh_2788

Provider ID #:.+?\d{3}\s$\d{3}$\s\d{3}\-\d+