Search code examples
functiongoogle-sheetssplitformula

How to split the text and date into 2 columns in google sheet?

enter image description here

Hi everyone,

I want to split the data in screenshot above into 2 columns. Currently the text and the date are combined in one column, my goal is to split the column into 2, so one column will be *AMC or *BMO and another column will be the date. For those cells without text (*AMC/*BMO), then the text column will be just empty.

I tried to use SPLIT function but there is no delimiter between the text and date, so I'm not sure how to split them. May I know is there any other way that I can use to split the column into 2? Any help will be greatly appreciated!

Solution

  • Assuming the data in column A, you could try

    =ArrayFormula(if(len(A2:A), TRIM(regexextract(" "&to_text(A2:A),  "(.+?|\s)(\d+\/\d+\/\d+)")),))

    Change range to suit.

    enter image description here

    Alternatively, you can also try

    =ArrayFormula(if(len(B2:B), {iferror(regexextract(" "&to_text(B2:B),  "[A-Z*]+")), --regexextract(" "&to_text(B2:B),  "(\d+\/\d+\/\d+)")},))

    The latter option will make it possible to format the date as you wish.