Excel split

Reply #19

SergeM — Thu, 08 May 2014 14:55:43 GMT

Assignment: Split some text to characters. Put the characters into cells.

x 1 2 3

123 1

2 3

abc a b c

Use the worksheetfunction MID().

=MID($A2,B$1,1

Reply #18

SergeM — Tue, 06 May 2014 19:59:57 GMT

Mini VBA workshop

Tired of nesting? Why not create your own function?

Instead of nesting two Transpose() functions, we can do the nesting with VBA in the VBE.

'Returns an array or matrix m*n

Public Function ToMatrixMN(TheRange)

ToMatrixMN = WorksheetFunction.Transpose(WorksheetFunction.Transpose(TheRange))

End Function

This can also be written as follows:

'Returns an array or matrix m*n

Public Function ToMatrixMN(ByVal TheRange As Range) As Variant

ToMatrixMN = Work... [ More ]

Reply #17

SergeM — Mon, 05 May 2014 18:47:17 GMT

Small concatentation workshop with addin function JoinElements(rowmatrix,delimiter)

A B C D E F

1 1 2 3 4 5 1-2-3-4-5

2 5 6 7 8 9 5-6-7-8-9

3 {=JoinElements(TRANSPONEREN(TRANSPONEREN(A1:E1)); - )}

4 {=JoinElements(TRANSPOSE(TRANSPOSE(A1:E1)), - )}

You need two nested Transpose() functions to generate a rowmatrix from a row of cell values.

Add a delimiter like for example - . Push CTRL+SHIFT+ENTER to finish the cell input.

The function will work better than

Reply #16

SergeM — Fri, 02 May 2014 21:00:30 GMT

Your smart picks master guide that just didn't do it for you?

I think that the wheels do it, not the picks.

Reply #15

SergeM — Fri, 25 Apr 2014 16:27:08 GMT

A^^(-1).A=I

Reply #14

LottoBoner — Wed, 23 Apr 2014 21:52:30 GMT

Yes I remember this from the Lottery Master Guide!

I think it went something like

A^2 + B^2 = C^2

Reply #13

LottoBoner — Wed, 23 Apr 2014 21:51:23 GMT

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

Reply #12

SergeM — Tue, 22 Apr 2014 14:14:11 GMT

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.

Reply #11

SergeM — Tue, 22 Apr 2014 13:57:30 GMT

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 isola... [ More ]

Reply #10

SergeM — Mon, 21 Apr 2014 22:02:09 GMT

Tip:

Make a webquery on some lottery website.

Apply the split function to the query results

Reply #9

SergeM — Sun, 20 Apr 2014 22:28:10 GMT

Example special case separator: /*

1/* 2/* 3

1 2 3

Reply #8

SergeM — Sun, 20 Apr 2014 22:19:47 GMT

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

Reply #7

SergeM — Sun, 20 Apr 2014 22:10:20 GMT

Average:

39

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

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

Tip: Try every function

Reply #6

SergeM — Sun, 20 Apr 2014 22:04:17 GMT

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.

Reply #5

SergeM — Sun, 20 Apr 2014 21:58:56 GMT

\ 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,

Reply #4

SergeM — Sun, 20 Apr 2014 21:51:26 GMT

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

Reply #3

SergeM — Sun, 20 Apr 2014 21:35:59 GMT

Other example:

1/02/2003 1 2 2003

Reply #2

SergeM — Sun, 20 Apr 2014 21:13:41 GMT

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

Reply #1

basilio — Wed, 09 Apr 2014 02:15:55 GMT

Sweet, Thanks SergeM

Excel split

SergeM — Tue, 08 Apr 2014 23:22:20 GMT

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.