CODEWITHSUNDEEP
excelexcel-formula
Python account
Python account

Reputation: 437

Splitting a string within a cell by searching for multiple words?

Similar to this,

=RIGHT([@LinkToSharepointList],LEN([@LinkToSharepointList])-FIND("word1",[@LinkToSharepointList]))

But allowing for word1, word2, word3, etc..

The data looks like

"https://sharepoint/sites/folder_name/this_is_a_long_word1 01.02.2020.xlsx"
"https://sharepoint/sites/folder_name/this_is_a_long_word3 01 02 2020.xlsx"
"https://sharepoint/sites/folder_name/this_is_a_long 01022020.xlsx"

I'm trying to return the date, thanks

Upvotes: 1

Views: 88

Answers (1)

JvdV
JvdV

Reputation: 75840

Assuming:

  • The date is made up out of the 8 rightmost digits;
  • You are using ms365;
  • Your local settings would recognize a date formatted like "dd-mm-yyyy" or "mm-dd-yyyy").

You could try:

enter image description here

Formula in B2:

=DATEVALUE(TEXT(--RIGHT(CONCAT(IFERROR(--MID(A2,SEQUENCE(LEN(A2)),1),"")),8),"00-00-0000"))

Upvotes: 1

Related Questions