+ Reply to Thread
Results 1 to 8 of 8

String Move from Left to Right

  1. #1
    Registered User
    Join Date
    05-23-2012
    Location
    Phx, AZ
    MS-Off Ver
    Excel 2007
    Posts
    16

    String Move from Left to Right

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

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

    JAN
    JANS
    JANTX
    JANTXV

    One step further. If "TR" or "T/R" are at the end of the PN, then I need that removed completely.
    i.e.

    JANTX1N4624UR-1TR
    JANTX1N4625-1
    JANTX1N4625-1T/R

    PN vs. Desired result
    JANTX1N4624UR-1TR = 1N4624UR-1JANTX
    JANTX1N4625-1TR = 1N4625-1JANTX
    JANS2N1711T/R = 2N1711JANS
    JANTXV1N4148UR-1 = 1N4148UR-1JANTXV
    Last edited by Echo_711; 10-06-2014 at 07:49 PM.

  2. #2
    Forum Contributor
    Join Date
    05-04-2014
    Location
    New Jersey, United States
    MS-Off Ver
    Excel 2010, 2013
    Posts
    120

    Re: String Move from Left to Right

    This formula's pretty messy, but here's something that may work:

    =SUBSTITUTE(SUBSTITUTE(REPLACE(A1,1,MAX(IFERROR(SEARCH({0,0,"JAN","JANS","JANTX","JANTXV"},A1),)*COLUMN(A:F)),),"/",),"TR",)&INDEX({0,0,"JAN","JANS","JANTX","JANTXV"},MAX(IFERROR(SEARCH({0,0,"JAN","JANS","JANTX","JANTXV"},A1),)*COLUMN(A:F)))

    You need to confirm it with ctrl+shift+enter since it is an array formula. As it is, it refers to data in cell A1.

    I will work on this a little bit more to see if I can clean it up at all.

  3. #3
    Registered User
    Join Date
    05-23-2012
    Location
    Phx, AZ
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: String Move from Left to Right

    Thanks for your help! I have this below formula that could probably be worked in? I'm just not sure how...

    =COUNTIF(A2,"JAN*"&{"","S","TX","TXV"}&"*")),"JAN"&{"","S","TX","TXV"})

  4. #4
    Forum Contributor
    Join Date
    05-04-2014
    Location
    New Jersey, United States
    MS-Off Ver
    Excel 2010, 2013
    Posts
    120

    Re: String Move from Left to Right

    This formula should be a little more user-friendly:

    =SUBSTITUTE(SUBSTITUTE(REPLACE(A1,1,7-MATCH(1,SEARCH({"JANTXV","JANTX","JANS","JAN"},A1),),),"TR",),"T/R",)&INDEX({"JANTXV","JANTX","JANS","JAN"},MATCH(1,SEARCH({"JANTXV","JANTX","JANS","JAN"},A1),))

    Unlike the last one I gave, it doesn't require ctrl+shift+enter to use.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: String Move from Left to Right

    @clabulis, your formula in #4 does work as requested, but it DOES require CTRL+SHIFT+ENTER to activate the array.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  6. #6
    Forum Contributor
    Join Date
    05-04-2014
    Location
    New Jersey, United States
    MS-Off Ver
    Excel 2010, 2013
    Posts
    120

    Re: String Move from Left to Right

    Quote Originally Posted by JBeaucaire View Post
    @clabulis, your formula in #4 does work as requested, but it DOES require CTRL+SHIFT+ENTER to activate the array.
    All the text strings that the OP provided seem to be converted as requested without using CSE when I try it. Can you provide me with a string that causes my formula to fail?

    I see that it results in an error if no form of "JAN" is present in the string, but that can be remedied with an IFERROR.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: String Move from Left to Right

    If the PNs are in column A, this works at my end.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    It does not have to be array entered.

    A
    B
    C
    D
    1
    PNs
    Desired results
    Formula results
    2
    JANTX1N4624UR-1TR 1N4624UR-1JANTX 1N4624UR-1JANTX
    3
    JANTX1N4625-1TR 1N4625-1JANTX 1N4625-1JANTX
    4
    JANS2N1711T/R 2N1711JANS 2N1711JANS
    5
    JANTXV1N4148UR-1 1N4148UR-1JANTXV 1N4148UR-1JANTXV
    6
    Last edited by FlameRetired; 10-07-2014 at 11:39 AM. Reason: wrong cell reference in original formula

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: String Move from Left to Right

    Removed by FR. Double posted
    Last edited by FlameRetired; 10-06-2014 at 11:19 PM. Reason: double posted

+ 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. VBA to move data down a row and to the left
    By devilchild99 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-12-2013, 07:51 AM
  2. Move cells to the left
    By martinez_pedro in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-01-2011, 08:51 AM
  3. Hit Enter - move left not down
    By bridgeshop in forum Excel General
    Replies: 2
    Last Post: 05-15-2010, 03:49 AM
  4. Replies: 3
    Last Post: 04-03-2009, 06:52 AM
  5. How do I Move column A, B, etc. to left side.
    By TeesPlus in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-24-2006, 03:35 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