+ Reply to Thread
Results 1 to 6 of 6

"Right" function cut and move to the "Left"

  1. 08-01-2014, 09:31 AM #1
    Echo_711
    Echo_711 is offline
    Registered User
    Join Date
    05-23-2012
    Location
    Phx, AZ
    MS-Off Ver
    Excel 2007
    Posts
    16

    "Right" function cut and move to the "Left"

    Hi all! I need to move characters from the end of a string to the beginning.

    I have a list of Exact stings that are allowed. I need to move this allowed string to the beginning.

    JAN
    JANS
    JANTX
    JANTXV

    One step further if possible. If "TR" or "T/R" are at the end of the allowed Exact string, I need that removed completely.
    i.e.
    JANTR = JAN
    JANT/R = JAN
    JANTXT/R = JANTX

    PN Desired result
    1N3595UR-1JANTX = JANTX1N3595UR-1
    1N4992JAN = JAN1N4992
    2N1711JANS = JANS2N1711
    1N4148UR-1JANTXVTR = JANTXV1N4148UR-1
    Reply With QuoteRegister To Reply
  2. 08-01-2014, 09:50 AM #2
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: "Right" function cut and move to the "Left"

    Hi,

    =LOOKUP(2,1/(COUNTIF(A1,"*JAN"&{"","S","TX","TXV"}&"*")),"JAN"&{"","S","TX","TXV"})&REPLACE(A1,FIND("JAN",A1),99,"")

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  3. 08-01-2014, 10:04 AM #3
    Echo_711
    Echo_711 is offline
    Registered User
    Join Date
    05-23-2012
    Location
    Phx, AZ
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: "Right" function cut and move to the "Left"

    This is a perfect fix!!! Thank you!!
    Reply With QuoteRegister To Reply
  4. 08-01-2014, 10:09 AM #4
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: "Right" function cut and move to the "Left"

    You're welcome!

    Perhaps add an IFERROR to the formula so that it leaves the string unchanged if it contains none of those strings, i.e.:

    =IFERROR(LOOKUP(2,1/(COUNTIF(A5,"*JAN"&{"","S","TX","TXV"}&"*")),"JAN"&{"","S","TX","TXV"})&REPLACE(A5,FIND("JAN",A5),99,""),A5)

    Regards
    Reply With QuoteRegister To Reply
  5. 08-01-2014, 10:10 AM #5
    Echo_711
    Echo_711 is offline
    Registered User
    Join Date
    05-23-2012
    Location
    Phx, AZ
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: "Right" function cut and move to the "Left"

    Oh Nice! I was just working with it and realized I would need this.
    Reply With QuoteRegister To Reply
  6. 08-01-2014, 10:36 AM #6
    Echo_711
    Echo_711 is offline
    Registered User
    Join Date
    05-23-2012
    Location
    Phx, AZ
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: "Right" function cut and move to the "Left"

    XOR LX,
    What if I were to reverse this and take the allowed string from the Left and move it to the Right? In this situation I would still have TR and T/R that would need to be moved. Should I submit a new post for this question?

    PN vs. Desired result
    JANTX1N3595UR-1 = 1N3595UR-1JANTX
    JAN1N4992 = 1N4992JAN
    JANS2N1711T/R = 2N1711JANS
    JANTXV1N4148UR-1TR = 1N4148UR-1JANTXV
    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. Adding multiple "find" with "left" /"Right"
    By Daniel1 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 02-22-2014, 04:33 PM
  2. Don't know the Forumlas with problem either using "vlookup" "if" "and" or "index" "match"
    By Jokeman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  3. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  4. [SOLVED] If there is any text in column "A$" on "sheet1" then move cell to column "A$" on "sheet2"
    By ckgeary in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-27-2013, 08:28 PM
  5. HELP on "left","right","find","len","substitute" functions
    By serene83 in forum Excel General
    Replies: 5
    Last Post: 06-26-2006, 09:23 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