+ Reply to Thread
Results 1 to 5 of 5

Reverse Order while removing certain text

  1. #1
    Registered User
    Join Date
    01-13-2016
    Location
    Canada
    MS-Off Ver
    Office 2007
    Posts
    14

    Reverse Order while removing certain text

    Hi everyone! First post

    The below example in column 2 is what I'm looking to pull from column 1. I've been able to get the following to work on the "Sandra White" example, but not the other two. I'd like it so the names are extracted from column 1, and put into column 2. My current formula works on all examples, but I would like to strip out the "VIP" text, as currently any VIP names on the list are becoming "John VIP Smith", which isn't what I'm looking for. Unfortunately this much more selective stripping is a little beyond my skill as everything I've tried to do with my current approach gives me an error. Any help is greatly appreciated!

    Smith, John VIP John Smith
    Jackson, Miss VIP Miss Jackson
    White, Sandra Sandra White

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Reverse Order while removing certain text

    Data Range
    A
    B
    1
    Smith, John VIP
    John Smith
    2
    Jackson, Miss VIP
    Miss Jackson
    3
    White, Sandra
    Sandra White

    Data Range
    B
    1
    =IFERROR(TRIM(SUBSTITUTE(TRIM(MID(A1,FIND(",",A1)+1,255))&" "&LEFT(A1,FIND(",",A1)-1)," VIP"," ")),"")


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Reverse Order while removing certain text

    Please Login or Register  to view this content.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Registered User
    Join Date
    01-13-2016
    Location
    Canada
    MS-Off Ver
    Office 2007
    Posts
    14

    Re: Reverse Order while removing certain text

    Quote Originally Posted by :) Sixthsense :) View Post
    Data Range
    A
    B
    1
    Smith, John VIP
    John Smith
    2
    Jackson, Miss VIP
    Miss Jackson
    3
    White, Sandra
    Sandra White

    Data Range
    B
    1
    =IFERROR(TRIM(SUBSTITUTE(TRIM(MID(A1,FIND(",",A1)+1,255))&" "&LEFT(A1,FIND(",",A1)-1)," VIP"," ")),"")
    Thanks so much!
    Quick question, is the 255 just to cover the standard character length limit?

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Reverse Order while removing certain text

    Quote Originally Posted by bloodlvst View Post
    Quick question, is the 255 just to cover the standard character length limit?
    Yes, exactly. You can increase it based on your requirement.

+ 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. Columns appear in reverse order
    By potterdj in forum Excel General
    Replies: 3
    Last Post: 03-10-2015, 05:43 PM
  2. [SOLVED] reverse order
    By anilg0001 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 04-18-2013, 07:53 AM
  3. Replies: 5
    Last Post: 03-06-2008, 01:27 PM
  4. Replies: 3
    Last Post: 06-27-2006, 01:55 PM
  5. [SOLVED] reverse order
    By Lamb Chop in forum Excel General
    Replies: 4
    Last Post: 06-20-2006, 10:40 AM
  6. Reverse order in column
    By Ragdyer in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 07:05 AM
  7. [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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1