Reformatting names with special characters from LastName, FirstName MiddleInitial

[chellschaos]

Hi all! New to the forum, so please be gentle.
I've been working on this formula for over a week now, and I think I'm at the point where as I look at it to try and insert another function, nothing makes sense. It's all a jumble to me.

What I have is in Column A (with a header):
"Jones, Bob R"
"Smith, Sally A"
(and so on)

What I need as an output:
Bob Jones
Sally Smith

What I've gotten to so far:
Bob R " " Jones
Sally A " " Smith

with the following:
=IF((ISERROR(TRIM(MID(LEFT(A2&" ",SEARCH(" ? ",A2&" A ")),SEARCH(",",A2)+1,99)&LEFT(A2,SEARCH(",",A2)-1))))," ",(TRIM(MID(LEFT(A2&" ",SEARCH(" ? ",A2&" A ")),SEARCH(",",A2)+1,99)&LEFT(A2,SEARCH(",",A2)-1))))

I need the outputs to be blank in the instance that column A does not have any data in it.

Is there a better way to be doing this?
Thanks in advance!

ETA - File with examples - it's literally only what's above.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[Fluff13]

For those two examples try

Please Login or Register  to view this content.

[Glenn Kennedy]

One way (of many):

=LET(A,TEXTSPLIT(TEXTBEFORE(A1&" "," ",2),", "),IFERROR(TEXTJOIN(" ",,CHOOSECOLS(A,2,1)),""))

copied down

or, a dynamic array

=BYROW(A1:A4,LAMBDA(x,LET(A,TEXTSPLIT(TEXTBEFORE(x&" "," ",2),", "),IFERROR(TEXTJOIN(" ",,CHOOSECOLS(A,2,1)),""))))

[chellschaos]

Thank you so much for the quick response! I added the file (it's just the 2 lines I listed) for additional context. Your formula helps, but doesn't remove both instances of the " mark that surround the name.

[chellschaos]

Thank you so much for the quick reply, Glenn! I added the file (it's just the 2 lines I listed) for additional context if that might help.
Your formulas help (the second one blew my mind a little if I'm honest), but neither remove both instances of the " mark that surround the name. I realized after the fact that it may have appeared that I was listing the name as it appeared with the quotations around it, but the source actually has them listed that way and so I've listed them as such in the example file.
Is this a simple tweak to either of your formulas? Or does it complicate things further?

[Fluff13]

How about

Please Login or Register  to view this content.

[Glenn Kennedy]

OK. Try it again.

[chellschaos]

How about

Please Login or Register  to view this content.

That worked! Thank you so much!

[chellschaos]

OK. Try it again.

Above and beyond, Glenn! Thank you so very much!

[Fluff13]

Glad to help & thanks for the feedback.

[Glenn Kennedy]

Likewise...