How to rearrange words in Excel

Question

I have a problem for which I can't find a solution any where. I'm a newbie here and I hope somebody can answer my question. I have a excel sheet with data in the following format.

• Samsung Note 4 Book Cover
• Book Cover Samsung Galaxy S3
• Note 3 Samsung Book Cover

Like this I have over a 1000 items but I want to rearrange the words in this manner.

• Book Cover Samsung Note 4
• Book Cover Samsung Galaxy S3
• Book Cover Samsung Note 3

Please let me know how I can rearrange the words to get the result like this.

Answer 1

You can use substitue to replace "Book Cover" with nothing, ie remove it, then put it at the beginning using the & to concatentate it at the start.

="Book Cover " & SUBSTITUTE(A1,"Book Cover","",1)

Answer 2

=IF(LEFT(A1,10)="Book Cover",A1,CONCATENATE("Book Cover ",LEFT(A1,LEN(A1)-11)))

Note A1 is the column of the data in snippet above.