+ Reply to Thread
Results 1 to 15 of 15

Changing ordering of persons name in string

  1. #1
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Changing ordering of persons name in string

    In Cell A1 I have 'ROGAN J F' and I want the output to be 'J F'

    A1 could also be 'ROGAN JW' and I'd want the output to be 'JW'

    A1 could also be 'JONES DREW J' and I'd want the output to be 'J'

    A1 could also be 'JONES DREW X A' and I'd want the out to be 'X A'

    Basically I need a formula that looks for the first singular letter and returns everything to the right of it!

    Would appreciate help on any of the above as nothing I've found online seems to solve it I have attached a sample workbook for reference.
    Last edited by ScabbyDog; 01-27-2022 at 07:26 AM. Reason: To be more precise

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Changing ordering of persons name in string

    There are instructions at the top of the page explaining how to attach your sample workbook.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

    Administrative Note:

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Changing ordering of persons name in string

    Sorry, here it is attached.
    Attached Files Attached Files
    Last edited by ScabbyDog; 01-27-2022 at 07:26 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Changing ordering of persons name in string

    And your Excel version?

  5. #5
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Changing ordering of persons name in string

    MS365 Excel for MAC

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Changing ordering of persons name in string

    Then please update your profile, which says Excel 2017.
    Attached Images Attached Images

  7. #7
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Changing ordering of persons name in string

    Thanks Ali. That is now changed. Hopefully someone can assist me now!

  8. #8
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Changing ordering of persons name in string

    I've amended my post and uploaded excel sample book to be more precise in order to receive better help. Thanks all in advance.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Changing ordering of persons name in string

    Thanks. I am sure someone will chip in soon.

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

    Re: Changing ordering of persons name in string

    You have changed the requirements completely now. The solution I was working on was based on you having ROGAN J / JONES F and wanting J Rogan/F Jones as the output.

    Do you still want to do that?

    Pete

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Changing ordering of persons name in string

    I did not ask you to CHANGE the requirements here, I asked you to ADD to them.

    You have now effectively sabbotaged your own thread. I suggest you go back and put it right, adding EXTRA requirements at the end.

  12. #12
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Changing ordering of persons name in string

    Quote Originally Posted by Pete_UK View Post
    You have changed the requirements completely now. The solution I was working on was based on you having ROGAN J / JONES F and wanting J Rogan/F Jones as the output.

    Do you still want to do that?

    Pete
    Thanks Pete. Appreciate the response greatly. If you can just do the current requirements per the thread that's what's needed. Working with the / symbol actually is pretty straight forward and basic (pretty silly by me to bother to include such a simple thing originally) because you can just find all the text to the left and right of that and then concatenate it all together at the end so it's figuring out the 'Jones F W' type stuff now that is what's left.

    So far I've been able to count the characters in each word in a cell using the below where my data is in cell S2. If S2 contained 'ROGAN JONES F W' then the output of this formula would be '5 5 1 1'. Is there a way to then say find the first 1 and return it and all to the right of it?

    Please Login or Register  to view this content.
    Last edited by ScabbyDog; 01-27-2022 at 07:49 AM.

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

    Re: Changing ordering of persons name in string

    I'm not going to redo everything.

    Using your previous file, you can use this in F2 (or C2 if your prefer):

    =MID(LEFT(B2,FIND("/",B2)-1),FIND(" ",B2)+1,10)&PROPER(LEFT(B2,FIND(" ",B2)-1))&"/"&TRIM(MID(TRIM(MID(B2,FIND("/",B2)+1,100)),FIND(" ",TRIM(MID(B2,FIND("/",B2)+1,100)))+1,10))&" "&PROPER(LEFT(TRIM(MID(B2,FIND("/",B2)+1,100)),FIND(" ",TRIM(MID(B2,FIND("/",B2)+1,100)))-1))

    Copy down as required.

    Hope this helps.

    Pete

  14. #14
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Changing ordering of persons name in string

    Please try

    =MID(A2,SEARCH(" ? ",A2&" ")+1,9)
    Attached Files Attached Files

  15. #15
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Changing ordering of persons name in string

    Quote Originally Posted by Bo_Ry View Post
    Please try

    =MID(A2,SEARCH(" ? ",A2&" ")+1,9)
    Perfect! Much appreciated.
    Last edited by ScabbyDog; 01-27-2022 at 08:52 AM.

+ 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. Changing the connection string with VBA
    By fitzdesignz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-06-2018, 06:17 PM
  2. Replies: 0
    Last Post: 11-20-2015, 12:34 PM
  3. VBA Ordering User form for Purchase Ordering.
    By caf20012 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-10-2014, 01:50 PM
  4. [SOLVED] Changing colours of Part of string
    By dhiresh in forum Excel General
    Replies: 10
    Last Post: 03-04-2013, 05:31 AM
  5. Changing cell for string value
    By mineralfellow in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-26-2009, 12:49 PM
  6. Changing a string within a string
    By wilsonca in forum Excel General
    Replies: 1
    Last Post: 07-30-2008, 08:25 PM
  7. Changing a String to a Range
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-09-2006, 01:39 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