Google Sheets Problem w/ Substring Replacement Text Using =Left

Question

Trying to fix some malformed data. Here is the formula I'm using:

={"Final Name";ARRAYFORMULA(IFERROR(IF(ISBLANK(E3:E),,LEFT(E3:E,find(".",E3:E)-2))))}

Here is a sample of the data in colE. The list indent is not part of the data, it's just formating here for readability. I'm guessing I need some regex or something here but that's a little beyond my scope.

• CeeDee LambC. Lamb DAL
• J.K. DobbinsJ. Dobbins BAL
• Anthony McFarland Jr.A. McFarland PIT
• Michael Warren IIM. Warren PHI

The first produces CeeDee Lamb. That's perfect. The rest of them are not working - which is to be expected but I don't know how to fix.

• What I want it produce is: J.K. Dobbins
• What it actually produces is: (Blank Cell)
• What I want it to produce is: Anthony McFarland
• What it actually produces is: Anthony McFarland J
• What I want it to produce is: Michael Warren
• What it actually produces is: Michael Warren II

Any thoughts?

Answer 1

try:

=ARRAYFORMULA(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(
 ARRAY_CONSTRAIN(SPLIT(A1:A, " "), 9^9, 2))),, 9^9)), "[A-Z]?\.$", ))