Welcome Guest
You last visited December 10, 2016, 2:50 am
All times shown are
Eastern Time (GMT-5:00)

Excel split

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

 Page 2 of 2
Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: April 25, 2014, 12:27 pm - IP Logged

A^^(-1).A=I

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: May 2, 2014, 5:00 pm - IP Logged

Yes I remember this from the Lottery Master Guide!

I think it went something like

A^2 + B^2 = C^2

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

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

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: May 5, 2014, 2:47 pm - IP Logged

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 the built in concatenate function. This is an exagerated example to show the efficiency of the join function:

 1-2-3-4-5-6-7-8-9-10-11-12-13-14-15-16-17-18-19-20-21-22-23-24-25-26-27-28-29-30-31-32-33-34-35-36-37-38-39-40-41-42-43-44-45-46-47-48-49-50-51-52-53-54-55-56-57-58-59-60-61-62-63-64-65-66-67-68-69-70-71-72-73-74-75-76-77-78-79-80-81-82-83-84-85-86-87-88-89-90-91-92-93-94-95-96-97-98-99-100-101-102-103-104-105-106-107-108-109-110-111-112-113-114-115-116-117-118-119-120-121-122-123-124-125-126-127-128-129-130-131

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: May 6, 2014, 3:59 pm - IP Logged

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 = WorksheetFunction.Transpose(WorksheetFunction.Transpose(TheRange))
End Function

On the worksheet, type =ToMatrixMN(A1:B3) followed by CTRL+SHIFT+ENTER, to enter the function into one or more cells.

You can now do: =JoinElements(ToMatrixMN(A1:B1),"+").
If you want no separation between the elements, then use "" as separator.

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: May 8, 2014, 10:55 am - IP Logged

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)

 Page 2 of 2