Split cell with multiple spaces

[mgirdwood]

Hi
I am trying split a column of cells into two columns, however my data does not have specific delimiters. ie
Joe Blogs DeptA xxxx
Ian Smart DeptB xyxy

I need to split into 2 cells after the name and before the Dept

Any pointers would be welcome
Thanks

[takbb]

Hi @mgirdwood, can you tell us more about any patterns in your data to look for

e.g.

• does the department always start with “Dept”?
• is the name always two “words”?

[gonhaddock]

Hello @mgirdwood
You can test with a ‘Regex Split’ node with the following code:

(.+)(Dept.+) optionally (.+)((?i)Dept.+)

BR

[mgirdwood]

Hi @takbb
Unofrtunately there can be more or less words for “name”, the Department would always start with De

Not sure if that helps

[gonhaddock]

Hello @mgirdwood
This is a simplification from above proposal:

(.+)((?i)De.+)

BR

[takbb]

@mgirdwood

Joe Blogs DeptA xxxx
Ian Smart DeptB xyxy

@gonhaddock , I wonder if you might want to also ensure that De begins at a word boundary? putting (.+\b) in place of just (.+) - especially if case-insensitive.

@mgirdwood , unless you have a list of actual department names, it’s going to potentially be a bit “hit and miss”.

What if you have:

Ivan Denisovich Department for Siberian Vacations

but without additional information on valid names, or valid departments I’d say that what @gonhaddock has provided is the likely best solution

[mgirdwood]

Thanks everyone for the excellent suggestions, with the data I have the regex from @gonhaddock seems to give me the outputs i need

[gonhaddock]

Hello @takbb
I understand your concerns, my answer was generic to the challenge description.
However, the best way to ask in forum, is with a sharing sample of representative data, allowing us to scope the solution.

If the ‘De’ always start with upper case, I would avoid the operator (?i).

(.+)(\bDe.+)

This can be a better approach for the time being.

Regards

PS. Great about ‘Ivan Denisovich Department for Siberian Vacations’ ,
in this case there wouldn’t be a problem as the code is greedy; but what about
‘Ivan Denisovich Department for Denmark Vacations’ (?)