# Formula to move text to beginning of cell

1. ## Formula to move text to beginning of cell

Hi,
I have a list of movie titles in a format that I need to change. I need to move titles that end with ", The" or ", A" to the beginning of the text. For example:

"UNSPOKEN, THE"-> "THE UNSPOKEN"
"CURE FOR WELLNESS, A"-> "A CURE FOR WELLNESS"

Thanks.

2. ## Re: Formula to move text to beginning of cell

You can use a formula like this:

=IF(RIGHT(A1,4)=" THE","THE "&LEFT(A1,LEN(A1)-5),IF(LEFT(A1,2)=" A","A "&LEFT(A1,LEN(A1)-3),A1))

then copy down.

Hope this helps.

Pete

3. ## Re: Formula to move text to beginning of cell

Maybe this as well
Formula:
`Please Login or Register  to view this content.`

Ah, I believe your question was edited, since I 1st looked at it.

4. ## Re: Formula to move text to beginning of cell

Or this
Formula:
`Please Login or Register  to view this content.`

 v A B 1 UNSPOKEN, THE THE UNSPOKEN 2 CURE FOR WELLNESS, A A CURE FOR WELLNESS

5. ## Re: Formula to move text to beginning of cell

Let's hope we don't have an entry like:

Good, the Bad, the Ugly, The

Pete

6. ## Re: Formula to move text to beginning of cell

Thanks, Pete's formula didn't work titles with ", A"
Al's formula gave me errors when the title didn't have ", A" or ", The"

7. ## Re: Formula to move text to beginning of cell

Sorry, I put a LEFT when I meant a RIGHT - use this instead:

=IF(RIGHT(A1,4)=" THE",RIGHT(A1,3)&" "&LEFT(A1,LEN(A1)-5),IF(RIGHT(A1,2)=" A","A "&LEFT(A1,LEN(A1)-3),A1))

It will maintain the case of "The" at the end of the entry in A1.

Hope this helps.

Pete

8. ## Re: Formula to move text to beginning of cell

Originally Posted by yankeekid86
Thanks, Pete's formula didn't work titles with ", A"
Al's formula gave me errors when the title didn't have ", A" or ", The"
Comma is the only thing that could make any formula to work. If there is no comma present all formulas will fail. The reason why formula produced an error is due to inconsistency of your data.

9. ## Re: Formula to move text to beginning of cell

Originally Posted by AlKey
... due to inconsistency of your data.
If there is a film title that doesn't contain a "The" or "A" at the beginning (like "2001 - A Space Odyssey"), then there won't be one at the end. You just have to test for it and if it is not there then return column A directly.

Hope this helps.

Pete

10. ## Re: Formula to move text to beginning of cell

Try this
Formula:
`Please Login or Register  to view this content.`

11. ## Re: Formula to move text to beginning of cell

Still doesn't work with multiple commas like:

Good, the Bad, the Ugly, The

It removes all commas.

Pete

12. ## Re: Formula to move text to beginning of cell

How about finding the last comma in a string
Formula:
`Please Login or Register  to view this content.`

Oh Ya, I guess that could create other issues.

13. ## Re: Formula to move text to beginning of cell

Try it with:

I, Robot

or:

One Flew Over The Cuckoo's Nest

or:

Pirates of the Caribbean

Pete

14. ## Re: Formula to move text to beginning of cell

Oh ya,

``Please Login or Register  to view this content.``
Darn won't work for "I, robot."
It was a dumb movie anyway

15. ## Re: Formula to move text to beginning of cell

Well, the OP's gone quiet, but the amended formula that I posted in #7 seems to cope with all the examples I've tried.

Pete

16. ## Re: Formula to move text to beginning of cell

Suppose we use a lookup table like this in D:F. The possible ", prefix"; len(prefix); replacemennts

Then use this array formula.
Formula:
`Please Login or Register  to view this content.`

I haven't found a fail, yet. We could add more prefixes as they show up. Can't think of any more so far.

 A B C D E F 1 CURE FOR WELLNESS, THE THE CURE FOR WELLNESS , THE 5 THE 2 LION IN WINTER, A A LION IN WINTER , A 3 A 3 GET LOW GET LOW 4 GRADUATE, THE THE GRADUATE 5 GOOD, THE BAD, THE UGLY, THE THE GOOD, THE BAD, THE UGLY 6 I, ROBOT I, ROBOT 7 UNSPOKEN, THE THE UNSPOKEN 8 MATRIX, THE THE MATRIX 9 MONSTERS, INC. MONSTERS, INC. 10 SEVEN DEADLY SINS, THE THE SEVEN DEADLY SINS

17. ## Re: Formula to move text to beginning of cell

Originally Posted by Pete_UK
..... the amended formula that I posted in #7 seems to cope with all the examples I've tried.

Pete
Yup! And lots simpler than mine.

18. ## Re: Formula to move text to beginning of cell

Thanks guys, the formula in #7 worked! Saved me lots of time! Thanks again!

There are currently 1 users browsing this thread. (0 members and 1 guests)