Split to columns using key word

Question

I am trying to split a cell into separate columns using the keyword Long with a Regex tool as shown below, however the issue that I am running into is that when the tool finds the word along it uses that to split the cell.  Is there a way to prevent the tool from using along to split the cell so it is split in the correct place or another method I should use?  I have also included a small chart with some data to use.

Cell to be splitCurrent 1st Split CellCurrent 2nd Split CellDesired 1st Split CellDesired 2nd Split CellShort Term: Banker training and guidance criteria for TWC to be included along with WLS guidance Long Term: Early engagement of Trade as this is a Product Management and client preference direction (deal by deal) - Banker and WLS trainingShort Term: Banker training and guidance criteria for TWC to be included along with WLS guidance Long Term: Early engagement of Trade as this is a Product Management and client preference direction (deal by deal) - Banker and WLS trainingShort Term: Banker training and guidance criteria for TWC to be included along with WLS guidanceLong Term: Early engagement of Trade as this is a Product Management and client preference direction (deal by deal) - Banker and WLS training

Christina_H · Answer

You could also include \b to mark word boundaries:

(.*)(\bLong\b.*)

Or given your data, you could increase the search term to Long Term:

(.*)(Long Term.*)

Yoshiro_Fujimori · Answer

Hi @MattR79 ,

RegEx

(?<!a)long

will match 'long' but will not match with 'along'.

For detail, please check 'negative lookbehind' on the page below.

https://www.rexegg.com/regex-lookarounds.html

FinnCharlton · Answer

@MattR79 If 'Long' is always capitalised, untick 'Case Insensitive'?