+ Reply to Thread
Results 1 to 18 of 18

Formula to move text to beginning of cell

  1. #1
    Registered User
    Join Date
    09-13-2011
    Location
    NYC
    MS-Off Ver
    Excel 2016
    Posts
    36

    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.
    Last edited by yankeekid86; 10-26-2016 at 12:00 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,784

    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. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,242

    Re: Formula to move text to beginning of cell

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

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

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to move text to beginning of cell

    Or this
    Formula: copy to clipboard
    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
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,784

    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. #6
    Registered User
    Join Date
    09-13-2011
    Location
    NYC
    MS-Off Ver
    Excel 2016
    Posts
    36

    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. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,784

    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. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to move text to beginning of cell

    Quote Originally Posted by yankeekid86 View Post
    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. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,784

    Re: Formula to move text to beginning of cell

    Quote Originally Posted by AlKey View Post
    ... 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. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to move text to beginning of cell

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

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,784

    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. #12
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,242

    Re: Formula to move text to beginning of cell

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


    Oh Ya, I guess that could create other issues.

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,784

    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. #14
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,242

    Re: Formula to move text to beginning of cell

    Oh ya,
    How about an error trap?

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

  15. #15
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,784

    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. #16
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    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: copy to clipboard
    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
    Dave

  17. #17
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    Re: Formula to move text to beginning of cell

    Quote Originally Posted by Pete_UK View Post
    ..... 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. #18
    Registered User
    Join Date
    09-13-2011
    Location
    NYC
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: Formula to move text to beginning of cell

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Vba to make new text from cell change post at beginning of cell comment not the end
    By M38512P in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-04-2015, 11:23 AM
  2. [SOLVED] formula to place text, beginning from 4th slash from right
    By Marcin4111 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-01-2014, 04:49 AM
  3. Move Text From End Of String To Beginning
    By Tellm in forum Excel General
    Replies: 4
    Last Post: 06-28-2013, 06:09 AM
  4. Move text (the word "The") to beginning of cell
    By techstorm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-27-2012, 03:04 PM
  5. [SOLVED] Script or formula to move text from beginning to end of a string
    By robbyvegas in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-11-2012, 10:18 AM
  6. Replies: 2
    Last Post: 03-21-2011, 01:33 PM
  7. How do I add text at the beginning of each cell?
    By hlo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-21-2006, 12:30 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1