UC San Diego SearchMenu

Problem

A single column contains a Title and Date information which needs to be split into four columns: Title, Date, beginDate, endDate

Example cell contents:

Eldorado Gold Mines, Ltd. 1941
Polizeiliche Abmeldung. Adalbert "Bela" Szilard 1923
Polizeiliche Abmeldung 1920 - 1931
Detre, Rose - Leo Szilard's sister 1947 - 1962
Admiralty. (U.K.). Director of Naval Contracts 1936 - 1939

Solutions:

  • Assume your data starts on column A, row 2  
  • IFERROR prevents #VALUE form showing in results, returns empty cell instead

Get just the text before a date beginning with "19" (Title, Note, or some other text field)
=IFERROR(LEFT(A2, FIND("19",A2)-2), "")

Get a just year, or range of years, beginning with "19" (Date value (date label))
=IFERROR(RIGHT(A2, LEN(A2)-FIND("19",A2)+1), "")

Get just the first year of a date span, if years are separated by " - " (beginDate)
=IFERROR(TRIM(MID(A2,FIND("19",A2),4)), "")

Get just the last year of a date span beginning with "19", if years are separated by " - " AND if there is only a single date, use it. (endDate)
=IFERROR(IF(LEN(C2)=4, C2, TRIM(RIGHT(A2,LEN(A2)-FIND(" - 19",A2)-2))),"")

If Title ends with ',', use folder title, else use tile without dates
=IF(RIGHT(C2)=",",B2,C2)