Splitting a column at a variable position

Hi paul_cranberry - you can achieve this in power query editor and added the description for each step, please check below m code:

use below power query editor code as bleow:

let
// Step 1: Load the Data
Source = Table.FromRows(
{
{"Dr. Sam Karta Jr., CEO, Tallis Partners"},
{"Fred Sampson, III, Director, Aspen Institute"},
{"Ruth Kaufmann, PhD, Executive Director"},
{"Marci Cheng, CHRO"}
},
{"Column A"}
),

// Step 2: Define a List of Suffixes
Suffixes = {"Jr.", "III", "PhD"},

// Step 3: Identify Suffixes in the Text
AddSuffixFlag = Table.AddColumn(Source, "HasSuffix", each
List.AnyTrue(List.Transform(Suffixes, (suffix) => Text.Contains([Column A], suffix)))
),

// Step 4: Extract the Prefix (Name and Suffix)
ExtractPrefix = Table.AddColumn(AddSuffixFlag, "Prefix", each
if [HasSuffix] then
Text.BeforeDelimiter([Column A], List.First(List.RemoveNulls(List.Transform(Suffixes, (suffix) => if Text.Contains([Column A], suffix) then suffix else null))) & ",")
else
Text.BeforeDelimiter([Column A], ",", 0)
),

// Step 5: Extract the Job Title and Remaining Text
ExtractJobTitle = Table.AddColumn(ExtractPrefix, "JobTitle", each
if [HasSuffix] then
Text.TrimStart(Text.AfterDelimiter([Column A], List.First(List.RemoveNulls(List.Transform(Suffixes, (suffix) => if Text.Contains([Column A], suffix) then suffix else null))) & ","))
else
Text.TrimStart(Text.AfterDelimiter([Column A], ",", 0))
),

// Step 6: Clean Up Columns
RemoveColumns = Table.SelectColumns(ExtractJobTitle, {"Prefix", "JobTitle"}),

// Step 7: Rename Columns
RenameColumns = Table.RenameColumns(RemoveColumns, {{"Prefix", "Column A"}, {"JobTitle", "Column B"}})
in
RenameColumns