Step1 :
Find number of occurrences (this is the most important step in all this)
= LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””)) (What a great way to get the number of occurrences – very thoughtful!)
Step2:
Substitute the last occurrence only!
= SUBSTITUTE(A1,”,”,”|”, … ) (… follows from the function above which gives the instance to be substituted)
Step3:
Extract using the first formula above
RIGHT(A1, LEN(A1) – FIND(“|”,…,1))
Putting them all together –
=RIGHT(A1,LEN(A1)-FIND(“|”,SUBSTITUTE(A1,”,”,”|”,LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””)))))
