Concatenating your Balls

**DocAElstein** · 08-17-2014, 01:05 AM

**Excel Fox** · 08-21-2014, 12:01 AM

DocAElstein, welcome to the ExcelFox community.

The text to column feature in Excel is used to split text based on a delimiter, like spaces, commas (,) etc. I am almost certain that it doesn't work the other way round.

Now, coming to your specific query about Jill referring to the Text To Column feature to convert the comma separated ball numbers for each day, in to different rows, I assure you that was just one part of the conversion. In the interest of time, and to keep that separate from the overall objective of the podcast, Jill certainly omitted to showcase the entire method he adopted to convert the data to what he was showing in that podcast video.

What he would have done is, after pasting the comma separated data from MegaMillions site, split the data in to separate columns using Text To Column, and then used some formulae or VBA to make all data in to columns. That's my reading, and a lot of other Excel experts would agree. But, with due credit to Jill, I wish and hope that I am wrong

**Excel Fox** · 08-21-2014, 12:03 AM

For those interested to hear Jill's podcast which is being referred to in this thread, here's the video.

**DocAElstein** · 08-21-2014, 12:51 AM

Originally Posted by Excel Fox

DocAElstein, welcome to the ExcelFox community.

The text to column feature in Excel.................

Hi,
Thanks very much for replying. Happy to be here in the Forum!.
. I was mainly interested in a reply from Rick to my specific question on a line in his code as I had asked in #15 from the MrExcel thread ( Multiple Columns into Single Column using Data, Text to Column ), as I had not managed to catch him there.

. But nevertheless Thanks for the interesting infomation. I expect your infomation would have been of particular interest to the initiator of that Thread. (I could possibly referrence him here, but I am not too sure about the Forum Rules about that sort of thing? Maybe you can advise me on that one?)

. I had simply replied in that MrExcel Thread in parralel with Rick to that thread and had asked him for some clarification of one line in his code. Unfortunately it is difficult to get in touch with him.
. Thanks again.
. Hope I can be active as well in the future in this forum!

Alan Elston
Bavaria,
Germany

**Excel Fox** · 08-21-2014, 01:20 AM

OK. The conditional evaluation returns an array of values, whereas a direct non-conditional evaluation would return a single value 'in some cases'. There could be a valid theory behind that, but that is not something I am privy to. It's probably the way EVALUATE function works. So anyway, to understand that difference a bit more clearly, you can test the following to codes. Both are actually trying to do the same time, but since the evaluate function 'in some cases' only returns a single value in the second code, the entire output in A1:A10 becomes the same. Having said that, in this case below, both the codes seem to be working fine in some systems, and not in some other systems. I will get back to you with a suitable example.

Code:

Range("A1:A10") = Evaluate("IF(1," & Range("A1:z10").Columns(2).Address & "&"" - ""&" & Range("A1:z10").Columns(3).Address & "&"" - ""&" & Range("A1:z10").Columns(4).Address & ")")

Code:

Range("A1:A10") = Evaluate(Range("A1:z10").Columns(2).Address & "&"" - ""&" & Range("A1:z10").Columns(3).Address & "&"" - ""&" & Range("A1:z10").Columns(4).Address)

And by the way, you don't necessarily need to use ROW()

Rick just used that as a means to move the IF function's execution to the TRUE condition. Now, you would know that anything except 0 (zero) means TRUE in the Excel boolean world. And since the minimum value of ROW() is greater than 0 (yes, it's 1 and above), using ROW(), or using 1, has the same effect in the IF condition.

Hope this makes it clear.

EDIT: Will get back with more clarity.

**DocAElstein** · 08-21-2014, 01:42 AM

Thanks very much. I'll try that all out and keep watching this space!

**Excel Fox** · 08-21-2014, 01:48 AM

Here's another sample by Vishesh. There also he is using a similar logic. But when I tested it on my machine, the code works fine even if I remove the IF condition.

Evaluate (VBA) for Concatenation | ExcelExperts.com

Let me investigate that!

**Excel Fox** · 08-21-2014, 02:08 AM

OK, here's another thread that confirms my claim. Phew!

VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate | Useful Gyaan

Specifically, read the below excerpt

Suppose we’ve some text values in range A1:A10 and we want to extract and keep only the first three letters of the values in all cells of this range. We could try to do so using the below code:

Code:

    Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:B10")
    rngData = Evaluate("Left(" & rngData.Address & ",3)")

BUT, you’ll find it does not work. It will fill the whole range with first 3 letters of cell A1. The reason is that if the Excel function used in Evaluate does not accept an array, the Evaluate function will not return an array. So in order to make this function return an array we need to modify the code slightly like this:

Code:

    Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10")
    rngData = Evaluate("if(Row(1:10),left(" & rngData.Address & ",3))")

In this case, ROW(1:10) returns an array of numbers from 1 to 10. Any numeric value other than 0 returned from a logical function is considered as TRUE, So there are 10 vertical TRUE values. For each TRUE, it will return the corresponding cell’s value from A1:A10.

And the reason why it was working in the previous examples was probably because we weren't using any other function within the EVALUATE function. Hopefully, this makes more sense.

**Excel Fox** · 08-21-2014, 02:12 AM

By the way, you can reference this thread to the OP at the other forum, as long as you feel it is relevant, and is not intended to manipulate traffic. Hope that's as straight as it gets.

**DocAElstein** · 08-21-2014, 03:15 AM

Wow, great, Thanks for all that info. - I'll work me way throught it now (or maybe tomorrow night in my daily "VBA Hour" - It is nearly bed-time now here in Bavaria)

Thanks again
Alan

.P.s....

By the way, you can reference this thread to the OP at the other forum, as long as you feel it is relevant, and is not intended to manipulate traffic. Hope that's as straight as it gets.

...OK....(should be OK - Rick referrences this Forum in all his replies!)