+ Reply to Thread
Results 1 to 10 of 10

reverse the order of words with formula

  1. 03-15-2014, 09:08 PM #1
    Aquamore
    Aquamore is offline
    Registered User
    Join Date
    04-18-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    49

    reverse the order of words with formula

    Hi guys,

    what would be the easiest way to reverse the order of words entered in one cell? If Cell A1 says John Smith (one space in between)- how to get Smith John with a formula?

    Thanks!
    Reply With QuoteRegister To Reply
  2. 03-15-2014, 09:13 PM #2
    AlKey
    AlKey is offline
    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: reverse the order of words with formula

    =RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1)

    A
    B
    1
    		 Smith John John Smith
    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
    Reply With QuoteRegister To Reply
  3. 03-15-2014, 09:14 PM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: reverse the order of words with formula

    Mabe this?
    =MID(A1,FIND(" ",A1,1)+1,99)&" "&LEFT(A1,FIND(" ",A1,1)-1)

    If you want then split into 2 cells...
    B1=MID(A1,FIND(" ",A1,1)+1,99)
    C1=LEFT(A1,FIND(" ",A1,1)-1)

    edit: is there an echo in here AlKey?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 03-15-2014, 09:17 PM #4
    Aquamore
    Aquamore is offline
    Registered User
    Join Date
    04-18-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: reverse the order of words with formula

    Thank you AlKey and FDibbins! Both methods work!
    Reply With QuoteRegister To Reply
  5. 03-15-2014, 09:18 PM #5
    AlKey
    AlKey is offline
    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: reverse the order of words with formula

    Thanks for the feedback!
    Reply With QuoteRegister To Reply
  6. 03-15-2014, 09:19 PM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: reverse the order of words with formula

    Happy to help
    Reply With QuoteRegister To Reply
  7. 03-15-2014, 09:20 PM #7
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: reverse the order of words with formula

    Try this...

    Data Range
    A
    B
    1
    John Smith
    Smith John
    2
    Alex Van Halen
    Van Halen Alex
    3
    Oscar De La Hoya
    De La Hoya Oscar


    This formula entered in B1 and copied down:

    =MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  8. 03-15-2014, 09:27 PM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: reverse the order of words with formula

    Thats pretty slick Tony, and very sneaky
    Reply With QuoteRegister To Reply
  9. 03-15-2014, 09:35 PM #9
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: reverse the order of words with formula

    Posted in another thread just a couple of minutes ago:

    Quote Originally Posted by Tony Valko View Post
    And I like to use as few functions as possible to get the desired result.
    Reply With QuoteRegister To Reply
  10. 03-15-2014, 10:22 PM #10
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: reverse the order of words with formula

    Yup, I was following trhat 1 too
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Reverse order using formula
    By ganeshkumar in forum Excel General
    Replies: 6
    Last Post: 11-26-2013, 02:38 PM
  2. [SOLVED] reverse order
    By anilg0001 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 04-18-2013, 07:53 AM
  3. [SOLVED] Vlookup formula in a reverse order loop
    By JMaug in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-01-2012, 10:48 PM
  4. Reverse Words order in sentence
    By ElmerS in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-03-2010, 04:06 AM
  5. [SOLVED] Reverse the order?
    By davegb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-07-2005, 05:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1