Search code examples
arraysgoogle-sheetssplitmultiple-columns

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


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 specifically the last two Capitalized Characters (e.g. "ATR" & "RA" ) into two separate columns.

In example "ATRAAtara" would exist in 3 Columns

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

Solution

  • use:

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

    enter image description here