View original (French)

Reorder the contents of a cell into another one

Solved
Alice0194 -  

Hello everyone,

I would like to know if it would be possible to display in column H a number corresponding to the disorder/reorganization of column A. And this automatically, as soon as data is entered in columns A to F.

The content of the cell H should correspond to this (starting from column A): AAMMJJWXYZ

No dash, no slash, nothing at all, just the sequence of these values.

https://we.tl/t-V2OXKr6u28hw4XYo

Thanks in advance to those who will try to help :D

7 answers

  1.  Aliboron Martin Contributor

    Not sure I understood everything correctly, but I would put it in H2 (to copy down):

    =STXT(A2;7;2)&STXT(A2;4;2)&GAUCHE(A2;2)&DROITE(A2;4)

    Hello to you all!
    Bernard

    1
    1. cousinhub29

      Hello,

      Another solution

       =TEXT(DATEVALUE(LEFT(A3;8));"yyyymmdd")&RIGHT(A3;4)

      Have a good day

      1
      1. PapyLuc51
        Hello,

        To remove the "-" and "/" you just need to substitute them with nothing, i.e. "".

        For your case there are only these two characters to remove, so the function is:

        =SUBSTITUTE(SUBSTITUTE(A2,"/",""),"-","")

        If there are more elements to remove, nest as many SUBSTITUTE as necessary.

        Best regards
        0
        1. Alice0194 Author

          Hello,

          Thanks for this first feedback!

          However, this function removes the characters between the digits, but I also need to change the order of the digits, and even delete one of them.

          Using this function, I therefore get JJMMAA1WXYZ. But I should have AAMMJJWXYZ: the order is wrong, and the 1 has disappeared.

          Kind regards :)

          0
      2. danielc0

        Hello everyone,

        With Excel 365, in H2 to copy down:

         =IF(A2<>"";TEXTJOIN("",,REGEX.REPLACE(A2,"[-/]","",0));"")

        Daniel

        0
        2. danielc0

          Hello Bernard,

          In the end this is enough:

           =REGEX.REPLACE(A2,"[-/]","",0)
           =SUBSTITUTE(SUBSTITUTE(A2,"/",""),"-","")

          Daniel

          0
          1. Aliboron Martin Contributor

            Yes, Daniel, but in these two cases there is still the "1" that is in the 10th position and the date is not flipped to AAMMJJ. So I’ve "tinkered" around, but cousinhub29’s formula is more elegant (even if it doesn’t work if it doesn’t function for cell A2, but that isn’t important).

            0
        3. PapyLuc51

          Hello,

          Well seen Aliboron Martin, that is indeed what Alice0194. is expecting

          I couldn't download the file and the explanations in the initial question made it hard to understand the issue.

          Best regards to all

          0
          1. Alice0194 Author

            Thank you very much for your answers! The two formulas proposed by cousinhub29 and Aliboron Martin work!

            Sorry I wasn't very clear, it seemed that way when I wrote it! :D

            Thank you again and have a great day everyone!

            0