To fix a formula that is giving the wrong data

[Jumper23]

I am working with a list of names that are separated by a space following a semicolon.
The formula I have allows me to sort within a cell alphabetically and another formula that is supposed to remove the semicolon after the last name. The problem is the formula fails to remove the semicolon and adds a letter at the end of all the names.

This is what I am getting when I execute the formula comes out as this
"Adam;Y Kayla;Y Meghan;;Y Xavier;Zola;Y Yang;Y"

I do not want double semicolon Y after Meghan. I want to remove the letter and the semicolon at the end of the last name. I only want the names sorted alphabetically, separated by a semicolon following space, and no space or semicolon at the end of the last name.

I am trying to get the names sorted alphabetically within a cell and remove the semicolon at the end of the last name.

I have attached an excel file to show as an example.

[Vraag en antwoord]

Look for the differences

[Jumper23]

Look for the differences

Hello Vraag en antwoord,

I am confused what do you mean by the difference?

[Glenn Kennedy]

Try this...

=TEXTJOIN("; ",TRUE,SORT(FILTERXML("<A><B>"&SUBSTITUTE(SUBSTITUTE(A1,";","")," ","</B><B>")&"</B></A>","//B")))

[Glenn Kennedy]

Duhh. Scrap that, use this..

=TEXTJOIN("; ",TRUE,SORT(FILTERXML("<A><B>"&SUBSTITUTE(TRIM(SUBSTITUTE(A1,";"," "))," ","</B><B>")&"</B></A>","//B")))

[Jumper23]

Duhh. Scrap that, use this..

=TEXTJOIN("; ",TRUE,SORT(FILTERXML("<A><B>"&SUBSTITUTE(TRIM(SUBSTITUTE(A1,";"," "))," ","</B><B>")&"</B></A>","//B")))

Hello Glenn,

Thank you for this here. With the TRIM the semicolon is removed and with the SUBSTITUTE the semicolon and space are is that correct?

[Glenn Kennedy]

No. Substitute removes the; and trim replaces double spaces in the string with a single space and removes single spaces at the start and end.

[Jumper23]

No. Substitute removes the; and trim replaces double spaces in the string with a single space and removes single spaces at the start and end.

Ok, Thank you once again for the help and also for going into detail about it.

This is truly a big help