AnsweredAssumed Answered

Split imported field contents, with variable length, into two fields.

Question asked by garyjones on Jan 8, 2019
Latest reply on Jan 8, 2019 by garyjones



I was hoping someone could help me - for some reason I just can't seem to get my head around this.


I am importing data from an Excel sheet and need to split the contents of one field into two separate fields - one containing a four digit year date and the other the title of the publication.


As you will see from the four examples below there are formatting inconsistencies in the original data (spaces, missing data and so on) - over which I have no control.


American Journal of Critical Care, 2019

SBV Journal of Basic Clinical and Applied Health, 2018



Clinical and Applied Health 2018


The date field is done using an auto enter calculation having first applied Trim to remove any spaces at the end of the date.


However, I'm tying myself in knots with trying to extract just the title (obviously from the above only 3 would result). I've tried Left, Right, Substitute and Length. I thought the following with work but it only gives me the number;


Length ( TitleAndDate) - Left ( TitleAndDate; 4).


It's frustrating as I know it's simple.


All pointers appreciated.


Many thanks.


Best wishes