Welcome Guest
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
Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: April 8, 2014, 7:22 pm - IP Logged

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

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

Sweet, Thanks SergeM !

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 1 2 3 3
Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: April 20, 2014, 5:35 pm - IP Logged
Other example:
 1/02/2003 1 2 2003
Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: April 20, 2014, 5:51 pm - IP Logged
 Row-index: 2 3 1 4 1/12/12/20 12 12 12 12 f(x) {=INDEX(WAARDE(SplitToMatrix(A2;"/"));B\$1)}

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

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: April 20, 2014, 5:58 pm - IP Logged
 \ A B 1 001/123/12/20 2 1 {=WAARDE(TRANSPONEREN(SplitToMatrix(A1;"/"))} 3 123 4 12 5 20

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

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

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: April 20, 2014, 6:04 pm - IP Logged
 001/123/12/20 1 1 12 2 20 3 123 4

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.

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!

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: April 20, 2014, 6:19 pm - IP Logged
 Row-index: 2 3 1 4 1/12/12/20 12 12 12 12 f(x) {=INDEX(WAARDE(SplitToMatrix(A2;"/"));B\$1)}

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

 Rowindex: 2 3 1 4 1/123/12/20 123 12 1 20

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

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

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 1 2 3
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
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, 2014 05-06-29-35-51, Powerball: 21, Power Play: 5?Prize Payouts

Break off like follows.

 Sat, Apr 19, 2014 05-06-29-35-51, Powerball: 21, Power Play: 5?Prize Payouts Sat Apr 19 2014 Apr 19 05-06-29-35-51 Powerball: 21 Power Play: 5?Prize Payouts 05 06 29 35 51 Powerball 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.

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
 \ A B 1 001/123/12/20 2 1 {=WAARDE(TRANSPONEREN(SplitToMatrix(A1;"/"))} 3 123 4 12 5 20

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: 2 3 1 4 1/123/12/20 123 12 1 20

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

 Page 1 of 2