The TRIM function then removes all extra spaces and returns just the nth string. The total characters extracted is equal to the length of the full text string. The starting point is calculated with the code below, where N represents 'nth': (N-1)*LEN(A1)+1
Then the formula uses the MID function to extract the nth substring. In this snippet, the delimiter (delim) is replaced with a number of spaces equal to the total length of the string: SUBSTITUTE(A1,delim,REPT(' ',LEN(A1))) The gist of this formula is to replace a given delimiter with a large number of spaces using SUBSTITUTE and REPT, then use the MID function to extract text related to the 'nth occurrence' and the TRIM function to get rid of the extra space.
