Welcome Guest
Log In | Register )
You last visited December 10, 2016, 9:32 am
All times shown are
Eastern Time (GMT-5:00)

Excel split

Topic closed. 19 replies. Last post 3 years ago by SergeM.

Page 1 of 2
3.33
PrintE-mailLink
SergeM's avatar - slow icon.png
Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
Posted: April 8, 2014, 7:22 pm - IP Logged

Free addin to download: http://emuis.be/Excel/SM_20140520%20functions.zip

To return integers for mathematical use: Value(SplitToMatrix(cell_address;"-"))
CTRL + SHIFT + ENTER to get the matrix function working.

    basilio's avatar - tools

    United States
    Member #145207
    July 31, 2013
    94 Posts
    Offline
    Posted: April 8, 2014, 10:15 pm - IP Logged

    Sweet, Thanks SergeM !

      SergeM's avatar - slow icon.png
      Economy class
      Belgium
      Member #123700
      February 27, 2012
      4035 Posts
      Offline
      Posted: April 20, 2014, 5:13 pm - IP Logged

      NL

      Selecteer vier cellen rechts van cel A1, met 1-2-3-3, en tik =WAARDE(SplitToMatrix(A1;"-")), gevolgd door CTRL + SHIFT + ENTER.

       1-2-3-3

        SergeM's avatar - slow icon.png
        Economy class
        Belgium
        Member #123700
        February 27, 2012
        4035 Posts
        Offline
        Posted: April 20, 2014, 5:35 pm - IP Logged
        Other example:
        1/02/2003122003
          SergeM's avatar - slow icon.png
          Economy class
          Belgium
          Member #123700
          February 27, 2012
          4035 Posts
          Offline
          Posted: April 20, 2014, 5:51 pm - IP Logged
          Row-index:2314
          1/12/12/2012121212
          f(x){=INDEX(WAARDE(SplitToMatrix(A2;"/"));B$1)}

               EN: =index(value(splittomatrix(A2,"/")),B$1)

            SergeM's avatar - slow icon.png
            Economy class
            Belgium
            Member #123700
            February 27, 2012
            4035 Posts
            Offline
            Posted: April 20, 2014, 5:58 pm - IP Logged
            \AB
            1001/123/12/20
            21{=WAARDE(TRANSPONEREN(SplitToMatrix(A1;"/"))}
            3123
            412
            520

                   NL: {=WAARDE(TRANSPONEREN(SplitToMatrix(A1;"/"))}

                   EN: {=VALUE(TRANSPOSE(SplitToMatrix(A1,"/"))}

              SergeM's avatar - slow icon.png
              Economy class
              Belgium
              Member #123700
              February 27, 2012
              4035 Posts
              Offline
              Posted: April 20, 2014, 6:04 pm - IP Logged
              001/123/12/20
              11
              122
              203
              1234

              A1: 001/123/12/20

              NL: A2:A5: {=KLEINSTE(WAARDE(TRANSPONEREN(SplitToMatrix(A1;"/")));B2:B5)}

              EN: A2:A5: {=SMALL(VALUE(TRANSPOSE(SplitToMatrix(A1,"/"))),B2:B5)}

              Eventually use: $B$2:$B$5 or give the range a name.

                SergeM's avatar - slow icon.png
                Economy class
                Belgium
                Member #123700
                February 27, 2012
                4035 Posts
                Offline
                Posted: April 20, 2014, 6:10 pm - IP Logged

                Average:

                39

                NL: {=GEMIDDELDE(WAARDE(SplitToMatrix(A1;"/")))}

                En: {=AVERAGE(VALUE(SplitToMatrix(A1,"/")))}

                Tip: Try every function!

                  SergeM's avatar - slow icon.png
                  Economy class
                  Belgium
                  Member #123700
                  February 27, 2012
                  4035 Posts
                  Offline
                  Posted: April 20, 2014, 6:19 pm - IP Logged
                  Row-index:2314
                  1/12/12/2012121212
                  f(x){=INDEX(WAARDE(SplitToMatrix(A2;"/"));B$1)}

                       EN: =index(value(splittomatrix(A2,"/")),B$1)

                  Rowindex:2314
                  1/123/12/2012312120

                  NL: {=INDEX(WAARDE(SplitToMatrix(A2;"/"));B$1:E$1)}

                  EN: {=INDEX(VALUE(SplitToMatrix(A2,"/")),B$1:E$1)}

                   

                    SergeM's avatar - slow icon.png
                    Economy class
                    Belgium
                    Member #123700
                    February 27, 2012
                    4035 Posts
                    Offline
                    Posted: April 20, 2014, 6:28 pm - IP Logged
                    Example special case separator: "/* "
                    1/* 2/* 3
                    123
                      SergeM's avatar - slow icon.png
                      Economy class
                      Belgium
                      Member #123700
                      February 27, 2012
                      4035 Posts
                      Offline
                      Posted: April 21, 2014, 6:02 pm - IP Logged

                      Tip:

                      • Make a webquery on some lottery website.
                      • Apply the split function to the query results
                        SergeM's avatar - slow icon.png
                        Economy class
                        Belgium
                        Member #123700
                        February 27, 2012
                        4035 Posts
                        Offline
                        Posted: April 22, 2014, 9:57 am - IP Logged

                        Small copy and paste workshop

                        You just copied from the results of some webpage and you pasted to an Excel workbook.
                        Excel splitted the line into two cells.

                        Sat, Apr 19, 201405-06-29-35-51, Powerball: 21, Power Play: 5?Prize Payouts

                        Break off like follows.

                        Sat, Apr 19, 201405-06-29-35-51, Powerball: 21, Power Play: 5?Prize PayoutsSatApr 192014Apr1905-06-29-35-51Powerball: 21Power Play: 5?Prize Payouts0506293551Powerball 21

                        To get the powerball isolated, replace ":" by " ", and split by " ", using SUBSTITUTE(). You will get Powerball and 21 in two cells.

                        The last step would be ordering and optionally concatenating the obtained data on the same sheet or another sheet.
                        By ordering is meant using the equal sign, like =A7. You can also do =value(A7) or use the date function.

                          SergeM's avatar - slow icon.png
                          Economy class
                          Belgium
                          Member #123700
                          February 27, 2012
                          4035 Posts
                          Offline
                          Posted: April 22, 2014, 10:14 am - IP Logged

                          In order to make the task easier, you can use substitute() several times.

                          For example, for the second cell, replace ":" by "-". After that replace ", " by "-". Finally split by "-".
                          You can nest the functions or spread the steps over several cells.


                            United States
                            Member #124493
                            March 14, 2012
                            7023 Posts
                            Offline
                            Posted: April 23, 2014, 5:51 pm - IP Logged
                            \AB
                            1001/123/12/20
                            21{=WAARDE(TRANSPONEREN(SplitToMatrix(A1;"/"))}
                            3123
                            412
                            520

                                   NL: {=WAARDE(TRANSPONEREN(SplitToMatrix(A1;"/"))}

                                   EN: {=VALUE(TRANSPOSE(SplitToMatrix(A1,"/"))}

                            This is interesting, it kind of like Y=MX+B?


                              United States
                              Member #124493
                              March 14, 2012
                              7023 Posts
                              Offline
                              Posted: April 23, 2014, 5:52 pm - IP Logged
                              Rowindex:2314
                              1/123/12/2012312120

                              NL: {=INDEX(WAARDE(SplitToMatrix(A2;"/"));B$1:E$1)}

                              EN: {=INDEX(VALUE(SplitToMatrix(A2,"/")),B$1:E$1)}

                               

                              Yes I remember this from the Lottery Master Guide!

                              I think it went something like

                              A^2 + B^2 = C^2