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!
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?
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!
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?
Maybe:
NOT(CONTAINS(
INDEX( SPLIT( [column] , SUBSTITUTE(TEXT(YEAR(TODAY())) , "," , "" ) ) , 2 ) ,
SUBSTITUTE(TEXT(YEAR(TODAY())) , "," , "" )
))
User | Count |
---|---|
43 | |
32 | |
25 | |
23 | |
14 |