GoogleSheet: Split the last two Capitalized characters in a String into two columns-CodePudding

In examples, three cells contain these values:

ATRAAtara Biotherapeutics, Inc. PYPLPayPal Holdings, Inc. AABOSAcumen Pharmaceuticals, Inc.

QUESTION:

What is the GoogleSheet formula to 1) Target the first word (e.g. "ATRRAtara") to then 2) split into two columns specifcally the last two Capitalized Characters (e.g. "ATR" & "RA" ) into two separete columns.

In example "ATRAAtara" would exist in 3 Columns

Column1: ATRA Column2: Atara Column3: Biotherapeutics, Inc.

Thank you in advanced!

CodePudding user response：

Try this

=ArrayFormula(IF(A2:A="",,{ LEFT( TRIM(REGEXEXTRACT(REGEXEXTRACT(A2:A, "(\w )"),". [A-Z]")),LEN(TRIM(REGEXEXTRACT(REGEXEXTRACT(A2:A, "(\w )"),". [A-Z]")))-1), CONCAT(REGEXREPLACE(TRIM(REGEXEXTRACT(REGEXEXTRACT(A2:A, "(\w )"),". [A-Z]")),LEFT( TRIM(REGEXEXTRACT(REGEXEXTRACT(A2:A, "(\w )"),". [A-Z]")),LEN(TRIM(REGEXEXTRACT(REGEXEXTRACT(A2:A, "(\w )"),". [A-Z]")))-1),""),REGEXREPLACE(REGEXEXTRACT(A2:A, "(\w )"),TRIM(REGEXEXTRACT(REGEXEXTRACT(A2:A, "(\w )"),". [A-Z]")),"")), TRIM(REGEXREPLACE(A2:A,REGEXEXTRACT(A2:A, "(\w )"),""))}))

CodePudding user response：

use:

=ARRAYFORMULA(IFNA({
 REGEXEXTRACT(A1:A5, "([A-Z] )[A-Z]"), 
 REGEXEXTRACT(A1:A5, "[A-Z] ([A-Z]. ? )"), 
 REGEXEXTRACT(A1:A5, " (. )")}))