How can I move text from the center to the beginning of a cell in Google Sheets?

Other Questions Google Sheets
1

I’ve got a movie list in Google Sheets and I’m trying to fix the titles. Right now, some cells have titles like “Matrix, The (1999)” but I want them to say “The Matrix (1999)” instead. This needs to be done for a whole column.

Is there a way to do this automatically? I’m not great with formulas, so a simple method would be awesome. I’ve tried copy-pasting, but it’s taking forever with so many entries.

Here’s what I’m dealing with:

Current format:
A1: Lord of the Rings, The (2001)
A2: Godfather, The (1972)

Desired format:
A1: The Lord of the Rings (2001)
A2: The Godfather (1972)

Any tips on how to make this happen quickly? Thanks!

2

hey there! i had the same issue. try this formula: =IF(RIGHT(A1,5)=“, The”, "The "&LEFT(A1,LEN(A1)-6)&MID(A1,LEN(A1)-4,5),A1)

put it in B1 and drag down. it’ll move ‘The’ to the front for most titles. might need tweaking for odd cases tho. good luck with ur list!

3

You can actually accomplish this with a simple formula in Google Sheets. Here’s what you can do:

  1. In the column next to your movie titles (let’s say column B), enter this formula:

=REGEXEXTRACT(A1, “(.), The ((.))”)&" The "&REGEXEXTRACT(A1, “(.), The ((.))”)

  1. Drag this formula down to apply it to all your movie titles.

  2. Copy the results in column B and paste them as values back into column A.

This formula extracts the title and year, then rearranges them with ‘The’ at the beginning. It should work for most cases where ‘The’ is at the end of the title.

For titles without ‘The,’ you might need a slightly different approach, but this should cover a good chunk of your list. Hope this helps streamline your process!

4

I understand how annoying it is to have to manually adjust each title. I encountered a similar challenge with my own movie list. My approach was to start by entering a formula in a separate column to reformat each title. I used a formula like =IF(RIGHT(A1,5)=“, The”, "The " & LEFT(A1, LEN(A1)-5), A1), applied it down the column, and then used the Text to Columns feature to separate the year. Finally, I recombined the data. It wasn’t flawless and required some tweaking for special cases, but it saved me a lot of time.