Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

  1. #1
    New Member
    Join Date
    Nov 2008
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

    Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

    Help appreciated - completely lost!

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,818
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

    with PowerQuery (Get&Transform)

    raw result
    ENG/DEF/001/14 ENG/DEF/14/001


    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Split = Table.SplitColumn(Source, "raw", Splitter.SplitTextByAnyDelimiter({"/"}, QuoteStyle.Csv)),
        Merge = Table.CombineColumns(Split,{"raw.1", "raw.2", "raw.4", "raw.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"result")
    in
        Merge
    Last edited by sandy666; Jun 18th, 2019 at 02:35 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

    Or just =LEFT(A1,8)&MID(A1,13,2)&"/"&MID(A1,9,3) where A1 is the cell containing your source string.

    Helping you to Excel

  4. #4
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

    With your original text in A1, here's an ugly formula for B1 that will make the switch:

    Code:
    =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),2)))&RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),3)))&MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),2)),FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),2)))
    This one assumes the length of the characters between the slashes might vary.
    Last edited by kweaver; Jun 18th, 2019 at 02:53 PM.

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,232
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

    Quote Originally Posted by HelenL View Post
    Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001
    There are several ways to do this, some way more efficient than others but which might depend on the structure of your data, so I have some questions.

    1) Is this for a single cell (which one) or a column of cells (which column and start row)?

    2) Is the first field (the ENG) always 3 characters long?

    3) Is the second field (the DEF) always 3 characters long?

    4) Is the third field (the 001) always 3 digits long?

    5) Is the last field (the 14) always 2 digits long (meaning single digit numbers have leading zeros in order to make it 2 digits long)?

    6) Are you trying to physically change your data to this revised format directly within their cells (so if your example was in cell A1, the revised text would end up in cell A1)?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    New Member
    Join Date
    Nov 2008
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

    1) single cell A1 Row 2 but will pull the formula down through the rest
    2) yes
    3) yes
    4) yes
    5)yes this is the year
    6) yes

    Thank you !
    Last edited by HelenL; Jun 18th, 2019 at 03:48 PM.

  7. #7
    New Member
    Join Date
    Nov 2008
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

    Thank you so much that brilliant. works a treat !

  8. #8
    New Member
    Join Date
    Nov 2008
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

    Thank you. Much appreciated

  9. #9
    New Member
    Join Date
    Nov 2008
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

    Thank you. Much appreciated.

  10. #10
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,818
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

    Quote Originally Posted by HelenL View Post
    Thank you. Much appreciated.
    but which one?
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •