Split after a date

retailpartnerco · 03-10-2021 07:27 PM

Hi! I have a column called [Name/Date/Comment]. In this, users will write…

Nick B. - 12/02/2021
blah blah blah

Is there a way to only get the “blah blah blah” with an expression? Sometimes, the date is written differently, like these possibilities

Dec 12, 2020
Dec 12,2020
Dec 11/12 2020
Dec 12 2020
Dec 11&12, 2020

When I used the extract dates function with an index of “1” to get the first date in the [Name/Date/Comment] column, it was able to pick a date out of all of those possibilities above.

Is there a way to only get the blah blah blah? I tried…

Index(Split([Name/Date/Comment],Year(today())),2) in an email template, but it told me that I was using the split function incorrectly. Should I be using a different function, maybe substitute() ? Thanks!

Marc_Dillon

YEAR() is returning a Date type, but SPLIT() expects a Text type, which is why you get the error. You can convert it to Text by wrapping it in TEXT(). However, that also introduces a “thousands seperator” comma, for some odd reason (‘2021’ becomes ‘2,021’), so you also have to get rid of that. All in all:

INDEX( SPLIT( [column] , SUBSTITUTE(TEXT(YEAR(TODAY())) , "," , "" ) ) , 2 )

I noticed that there is a line break in your original example though. Will there always be one? You could split by the line break, as such:

INDEX(SPLIT( [column] , "
" ) , 2 )

But the bigger question is, why do you have 3 data points all jammed into 1 column to begin with?

retailpartnerco

Hey that worked! Thankyou.

Also to answer

, I have it to automatically fill in the name and date for the user, so they only have to go into the second line and start writing. Too many columns means too much scrolling for the user. Thanks again!

retailpartnerco

Also, I am trying to create a valid if formula for the Name/Date/Comment column. It is invalid if…
the current year appears in the text more than once. How should I write this formula?

Marc_Dillon

Maybe:

NOT(CONTAINS(
  INDEX( SPLIT( [column] , SUBSTITUTE(TEXT(YEAR(TODAY())) , "," , "" ) ) , 2 ) ,
  SUBSTITUTE(TEXT(YEAR(TODAY())) , "," , "" )
))