Rearrange words

I am trying to find an excel function that will change titles such as "The Greatest Day" to "Greatest Day, The" thus manipulating the title so that "The" is at the end of the phrase preceded by a comma. I know this should include a cell with the original text, a cell with an interim value, and a cell with the final text that includes the formula. Please help me do this!

Hi,
please take a moment to read the forum rules and then change your thread title to something more meaningful. Your title should describe your problem, so "rearranging words in a cell" might be a good start.

Thanks for changing the thread title.

how about this:

=IF(LEFT(A1,3)="The",MID(A1,5,999)&", The",IF(LEFT(A1,2)="a ",MID(A1,3,999)&", A",A1))

It works for both "the" and "A" at the beginning of a string

Put that formula in the cell where you want to see the result. The WHOLE formula, not just parts of it.

Let's say you placed the formula in B1. If you want, you can then copy cell B1 then right-click on A1 and choose PasteSpecial, select Values and click OK. You can then delete the formula in B1 and the new value in A1 will remain.

You can put the formula in any cell, but maybe you want to use B1. You copy and paste the whole formula into B1, then copy B1 down the list of values as far as they go in column A.

Like this (note, the --- are there to keep the spacing in place, you won't see that in the spreadsheet)

Please Login or Register  to view this content.

5 is to start at the fifth character in the string "The Great...", three for "the", one space
3 is to start a the third character in the string "A Wond....."

999 is a big number to ensure that all of the remaining string is returned. MID requires that you specify how many characters you want to return. I assume that you would not have more than 1000 characters in the string. Maybe 99 would also suffice.

cheers

=IF(LEFT(A1,3)="The",MID(A1,5,999)&", The",IF(LEFT(A1,2)="a ",MID(A1,3,999)&", A",A1))

The LEFT and MID functions shouldn't be case sensitive. It should find "The", "THE", "tHe", etc. just fine.

The formula provided does three things:

1. Check to see if the first 3 characters are "the". If so, move it to the end.
2. Check to see if the first 2 characters are "a ". If so, move it to the end.
3. If neither 1 or 2 are true, simply show the same value as in the cell referenced (A1 in example)

You shouldn't be seeing FALSE - except if the formula is referencing a blank cell, in which case it should show either 0 or FALSE. If you want to get rid of those, change the formula to:

=IF(A1="","",IF(LEFT(A1,3)="The",MID(A1,5,999)&", The",IF(LEFT(A1,2)="a ",MID(A1,3,999)&", A",A1)))

Ok,

with your phrase in A1 and any word or phrase you specify in B1

=IF(A1="","",IF(LEFT(A1,LEN(B1))=B1,MID(A1,LEN(B1)+2,999)&", "&B1,A1))

A1= Tiger and the rest of the world
B1= Tiger

result = and the rest of the world, Tiger

A1 = Tiger and the rest of the world
B1 = Tiger and the

result = rest of the world, Tiger and the

You can have a phrase of more than one words in B1

Does that help?