Selected Answer
If you have to get this from a cell that has the data in that exact format stored as text then I suggest that you do it this way:
- Split the data up using Text-to-Columns (Data tab > Text to Columns).
Split the data on the comma.
- Then, use this formula to get the number of days:
=(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)*365+IF(MOD(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1),4)=0,(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1))/4,0))+(LEFT(TRIM(B1),FIND(" ",TRIM(B1))-1)*7)+LEFT(TRIM(C1),FIND(" ",TRIM(C1))-1)
This is a big formula and it assumes that you have the data starting in column A row 1.
This will get the days and then you just use regular concatenation to get the other data.
Including the concatenation of the hours and minutes, the final formula, following the above example, could look like this:
=(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)*365+IF(MOD(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1),4)=0,(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1))/4,0))+(LEFT(TRIM(B1),FIND(" ",TRIM(B1))-1)*7)+LEFT(TRIM(C1),FIND(" ",TRIM(C1))-1)&" days, "&LEFT(TRIM(D1),FIND(" ",TRIM(D1))-1)&" hours, "&LEFT(TRIM(E1),FIND(" ",TRIM(E1))-1)&" minutes"
Also, the above formulas should account for leap years by adding 1 day for every 4 years in the 'years part' of the data.