+ Reply to Thread
Results 1 to 4 of 4

Formula for string edition

  1. #1
    Registered User
    Join Date
    11-07-2012
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    53

    Smile Formula for string edition

    Hi Guys, how are you today?

    So, in short, I have the following set of strings:

    Ronaldo, (Portugal) Christiano
    Beckam, David (England)

    and I want to receive the following:

    Christiano Ronaldo
    David Beckam

    I am aware how to do it without the parenthesis, just the flip:
    =MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1))

    I know also how to show only the text in the parenthesis:
    =MID(L15,FIND("(",L15)+1,(FIND(")",L15)-FIND("(",L15))-1)

    Any idea how to remove the parenthesis and the text inside and then to flip first name and last name?

  2. #2
    Registered User
    Join Date
    11-07-2012
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: Formula for string edition

    And I managed to do it myself 3 minutes after posting It is actually quite a big formula, so I have made it into two cells, otherwise it is impossible (for me):
    First cell, removing the parenthesis:
    =TRIM(LEFT(L2,SEARCH("(",L2&"(")-1))
    Second cell, switching the names:
    =MID(N2&" "&N2,FIND(" ",N2)+1,LEN(N2)-1)

    Anyway, if you want to help me with some ideas how to incorporate the two formulas without writing something ugly like:
    =MID(TRIM(LEFT(L2,SEARCH("(",L2&"(")-1))&" "&TRIM(LEFT(L2,SEARCH("(",L2&"(")-1)),FIND(" ",TRIM(LEFT(L2,SEARCH("(",L2&"(")-1)))+1,LEN(TRIM(LEFT(L2,SEARCH("(",L2&"(")-1)))-1)
    I would be glad to hear it

  3. #3
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    554

    Re: Formula for string edition

    =trim(right(substitute(replace(a1,find("(",a1),(find(")",a1)-find("(",a1))+1,),",",rept(" ",len(a1))),len(a1)))&" "&trim(left(substitute(replace(a1,find("(",a1),(find(")",a1)-find("(",a1))+1,),",",rept(" ",len(a1))),len(a1)))

  4. #4
    Registered User
    Join Date
    11-07-2012
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: Formula for string edition

    Good work, Ghozi Alkatiri!

+ 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. Index formula won't work in French edition
    By amy22x3 in forum Excel General
    Replies: 5
    Last Post: 02-23-2008, 03:45 PM
  2. [SOLVED] Why can't I use XML in the Student and Teacher Edition?
    By gunnyusmcret in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 01-26-2006, 04:30 PM
  3. Why can't I use XML in the Student and Teacher Edition?
    By gunnyusmcret in forum Excel General
    Replies: 6
    Last Post: 01-26-2006, 04:26 PM
  4. Why can't I use XML in the Student and Teacher Edition?
    By gunnyusmcret in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-26-2006, 02:00 PM
  5. how do i get excel on xp home edition
    By Elayne in forum Excel General
    Replies: 1
    Last Post: 07-04-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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1