Split column by word

[joooffice]

Hi,

I have a column containing the names of parents and children merged together that i would like to split into the child and parents names. The split should come before the 'son of' or 'daughter of' but because the number of characters before and after changes and there may or may not be a space between first name and surname before son/daughter I can't split by delimeter. If I have the first name and surname of the child before the son/daughter I need to the keep the whole name so taking just the first word wouldnt work either.

Ideally I want to define " "&"son" as my delimeter but I can't work out how to do this (or even just "son" and then I could trim the extra space)

Thanks

Joe Bloggs son of Dave

Angela Bloggs daughter of Sue

Jack son of Bob

Jason Smith son of Barbara Smith

Claire daughter of Jon

[Anonymous · Accepted solution]

try this:

Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByAnyDelimiter({"son of","daughter of"}, QuoteStyle.Csv), {"children", "parent"})

[joooffice]

Thanks, so obvious now you've shown me!