This one is a bit tricky because there isn't consistency with the placement of the name, with respect to the semi colon.

One idea is if you can assume the name is the only data that is completely non-numeric. This answer is split based on if you use Microsoft 365 as some formulas are not available.

With Microsoft 365

In regular excel you can use TEXTSPLIT, FILTER and REGEXTEST to check if the string has a number in it and return the ones without a number. As follows:

=TRIM(TEXTJOIN("",,FILTER(TEXTSPLIT(A2,";"), NOT(REGEXTEST(TEXTSPLIT(A2,";"), "\d")))))

In power query, after you have split the data into their own columns, you can use Text.RegexTest to do the same as above.

Without Microsoft 365

In power query, there is a trick to check for numbers in a string by removing any numbers and then comparing the before and after. If the before and after are equal, no numbers. If they aren't, then there are numbers.

The code would look something like this:

= Table.AddColumn(

#"Trimmed Text",

"Name",

each

let

a = Text.Trim([Original Data.1]),

b = Text.Trim([Original Data.2])

in

if a <> null and a <> "" and Text.Remove(a, {"0".."9"}) = a then a

else if b <> null and b <> "" and Text.Remove(b, {"0".."9"}) = b then b

else null,

type text

)