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
@binuacs 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
@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+,