Forum Discussion
Formula for a cell
Can anyone suggest what is the formula to take a name in one cell, like 'John & Jane Doe' and get into another single cell as 'Doe, John & Jane'?
Thanks!
1 Reply
Hi Ann,
First, Flash Fill https://support.office.com/en-us/article/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7 could work.
If with formula and the logic is extract the word after last space, end it with comma and combine with first part of the text, the formula could be
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100)) & ", " & LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))))How to extract last word in the string (first part of the formula) is explained here https://exceljet.net/formula/get-last-word
