Calling All Excel Wizards!! (????)

I need some assistance with MS Excel. First, a little bit about me (the user). I am an very advanced user of Excel (and computers in general). My only reason for mentioning this is not to toot my horn, but rather to be very specific about what I am trying to accomplish here. What I am trying to do is very, very, specific, and there are a whole lot of things (easier explanations) which I am NOT trying to do.

Problem - I have a VERY large number (quite possibly the single largest number in the universe...Pi). I am trying to parse this number. I am principally interested in ONLY the non-terminating, non-repeating, decimal portion of Pi (i.e. the x.1415976... portion, not the '3.x' part). (I can deal with the '3' if I have to, but this is not what I'm interested in). I would like to parse the decimal portion of Pi up into 2 digit strings (i.e. 14, 15, 92, etc.). I am working with roughly 1,000 digits behind the decimal point (more if I can figure out how to do this). I need an automated way to parse the decimal portion of Pi in this manner. But there's more... I then want to do the same thing again (another iteration) in groups of 3 digit strings (i.e. 141, 592, etc.). And again, in groups of 9 digits, 10 digits and 12 digits.

As mentioned, I am working with 1,000 to 5,000 places behind the decimal (a very large string indeed, right). I also want to take the result of each iteration and have it appear in a separate row (not column) of one column in the spreadsheet (getting the results into a column can be a separate step). In other words, I want the result of the 2 digit parse to appear as a series of rows in one column, and the result of the 3 digit parse as a series of rows in another column and the same for the 9, 10 and 12 digit parses.

Things I have already tried / other considerations - First, there are NO spaces or delimiters of any kind in the raw starting number, just a gi-normous string which goes on for thousands of digits. I have already tried "Text to Columns", and this works, BUT it is far too tedious for what I'm trying to do because you have to set each parse point manually (I'm looking for an automated way to tackle this). I've tried the "LEFT, RIGHT, MID" function, and there may be a solution with this, but I haven't discovered it so far (hopefully you may know a way). Everything I've tried with this function really wants a space or some form of a delimiter to work properly.

I will likely want to do other iterations beyond those mentioned in the future, so some flexibility and ease of use would be great.

Example starting point - As an example, my starting point will look something like this...in cell A1 I will have a 1,000 digit number. If I were to use "Text to Columns" and 2 digit parse for 100 parses I would then have cell A1=14, cell A2=15, cell A3=92 and so on for 97 more columns. My ideal end configuration would be to then make all of those columns into rows, so cell A1=14, cell B1=15, cell C1=92 and so on for 97 more rows.

Note - I've found all manner of videos and instructions on how to parse lists of peoples names for example, but this is not what I'm trying to do. Plus, all of those usually have a space, comma or some other form of a delimiter which makes things much easier, not to mention these are usually already in row orientation when the parsing begins. I don't have this luxury.

Reward - There is a reward for your help even! If my theories are correct, and you help me prove them by getting me to a good starting point, your reward just may be that you will have the satisfaction of being part of...SAVING ALL OF HUMANITY! (I know, it's a crummy reward, but it's something). Seriously though, this may actually have some pretty revolutionary outcomes possibly bordering on rewriting some elements of mathematics and physics.

Any help you can provide, ATS, would be GREATLY appreciated!!!

P.S. I can't think of a smarter group of folks to ask this question to.

Thanks, again!!

And just to get your creative juices flowing, I will give some insight into what my research is trying to accomplish...

I recently discovered something which leads me to conclude the decimal portion of Pi really does repeat, but not in the ways mathematicians have historically believed it should. I further believe I can prove this, but I need small equal length chunks of the remainder to work with. The computations on each chunk are very complex, with some fairly high order math being applied to each piece of data. The results are then compared to similar computations run on a data set with a different length.

I've stated that I am working with 1,000 to 5,000 digits of the remainder. The reason for this is because I believe Pi can be demonstrated to repeat within a data set of this size. I will try increasingly larger data sets as the situation dictates, but I intuitively feel the value is diminished if the data set gets too large.

So now you know...the rest of the story.

Thanks!

And just for fun, here's just a small slice of Pi for your efforts....

3.141592653589793238462643383279502884197169399375105820974944592*64062862089986280348253421170679821480865132823066470938446095505822317253594081 284811174502841027019385211055596446229489549303819644288109756659334461284756482337867831652712019091456485669234603486104543266482133936072602491412 737245870066063155881748815209209628292540917153643678925903600113305305488204665213841469519415116094330572703657595919530921861173819326117931051185 480744623799627495673518857527248912279381830119491298336733624406566430860213949463952247371907021798609437027705392171762931767523846748184676694051 320005681271452635608277857713427577896091736371787214684409012249534301465495853710507922796892589235420199561121290219608640344181598136297747713099 605187072113499999983729780499510597317328160963185950244594553469083026425223082533446850352619311881710100031378387528865875332083814206171776691473 035982534904287554687311595628638823537875937519577818577805321712268066130019278766111959092164201989380952572010654858632788659361533818279682303019 520353018529689957736225994138912497217752834791315155748572424541506959508295331168617278558890750983817546374649393192550604009277016711390098488240 128583616035637076601047101819429555961989467678374494482553797747268471040475346462080466842590694912933136770289891521047521620569660240580381501935 112533824300355876402474964732639141992726042699227967823547816360093417216412199245863150302861829745557067498385054945885869269956909272107975093029 553211653449872027559602364806654991198818347977535663698074265425278625518184175746728909777727938000816470600161452491921732172147723501414419735685 481613611573525521334757418494684385233239073941433345477624168625189835694855620992192221842725502542568876717904946016534668049886272327917860857843 838279679766814541009538837863609506800642251252051173929848960841284886269456042419652850222106611863067442786220391949450471237137869609563643719172 874677646575739624138908658326459958133904780275900994657640789512694683983525957098258226205224894077267194782684826014769909026401363944374553050682 034962524517493996514314298091906592509372216964615157098583874105978859597729754989301617539284681382686838689427741559918559252459539594310499725246 808459872736446958486538367362226260991246080512438843904512441365497627807977156914359977001296160894416948685558484063534220722258284886481584560285 060168427394522674676788952521385225499546667278239864565961163548862305774564980355936345681743241125150760694794510965960940252288797108931456691368 672287489405601015033086179286809208747609178249385890097149096759852613655497818931297848216829989487226588048575640142704775551323796414515237462343 645428584447952658678210511413547357395231134271661021359695362314429524849371871101457654035902799344037420073105785390621983874478084784896833214457 138687519435064302184531910484810053706146806749192781911979399520614196634287544406437451237181921799983910159195618146751426912397489409071864942319 ...

Unfortunately, I think I'm going to offer the kind of response I dread most.....

I am a software and database engineer. I specialize in the financials sector of applications, which of course, is accounting mostly.

The one thought in my head upon reading your post, was why MS Excel? Why not a programming language? Truth of the matter is, Excel...along with most if not all programs, have their limitations. Usually limitations that are built in.

So... are you in need of software (programming language, compiler, etc) to achieve this, and Excel is your best bet given what is at hand? You sound to have a good bit of skill, do you possess programming skills?


I almost always find myself in situations where I am trying to do something, within specific parameters, because of what I have specifically been tasked with. If a prettier path could have been had, an easier path, I definitely would have taken it. And whenever I end up posting on something like stackoverflow.com, the assinine responses I get...well....those responses are what is making me think I'm giving you something close to that same response here.

So, instead....I'm going to offer this. Send me a message. I have large unix and windows servers at my disposal, databases, and a variety of programming languages and tools. Let's talk...maybe I can be of real help with my own set of skills and the resources I have available at my disposal......

a reply to: Flyingclaydisk

The only thing i can think of is tedious multi-step processes beginning with fixed width delimiting and manual manipulation of the resulting Excel file.

The one thing i never messed with was macros. Do you use macros? Maybe with a lesser amount of manual labor it could be done? (Guessing you've already considered this.)

Good luck, I'm guessing there's a way. I've often resorted to giving up on there being a good way and choosing time-wasting tedium. It's miserable, but sometimes you get to the end result sooner than the time it takes to get the more efficient method figured out.

I'm forwarding the question on to a couple friends who might have further insight. If they come up with anything, I'll report back.

a reply to: nullafides

Yeah, it's pretty much a "go with what ya got" kind of thing. Unfortunately, programming is not my strong suit. I was pretty good with BASIC and a little FORTRAN, but that's where it ended. Electronics, math and physics are my area of specialization. I figured Excel probably wasn't a very good solution, but it's what I had. I figured I could at least get started and get some of the heavy lifting out of the way calculation wise.

I appreciate your offer and will send you a note.

Thanks!!

a reply to: Flyingclaydisk
hacking smth together right now... give me 10 minutes

a reply to: Flyingclaydisk

If I were in your shoes, and had the limitation of Excel, but....given my programming skills, I'd go with VBA....as Excel is designed to run on that when specified.

But, what concerns me, is the ability to handle the pure size of number you're looking at.

When you spoke of parsing the numbers out, you've got a good idea there. Get the size of the digits into much more accommodating sizes. From there, work on the individual threads.

My thought is that an enterprise scale database, like what I have access to, would do fairly well at this.

a reply to: verschickter

Please provide original string. I´ve added filter options for * and spacer but it still crashes. I come back in two hours, have to drive someone to the trainstation but the tool stands, it´s just the input string, it will give you excel sheet like you want it.

ok, got it to work, U2U me... I´ll answer in two hours...

a reply to: nullafides

Yeah, MS Excel really chokes on calculations with a number larger than 9 septillion. In some cases the numbers involved in the calculations are orders of magnitude larger than that so I have to convert them to decimal equivalents so Excel doesn't burn down.

a reply to: verschickter

Sent you a message also.

Thanks!

All,

NOTE - BTW...I have now confirmed that you cannot 'copy' the dataset posted here (on this thread) reliably. It is corrupt for some reason. There are other characters inserted and/or replacing digits on a "copy / paste" action.

a reply to: verschickter

mailer daemon with your provided adress...

###snip
yes, i noticed the dataset is corrupt but I wrote filters to sort the * and " "s out.

And something related.
I also want to comment on using excel sheets from unknown sources (including me), always be careful before opening, and always do a scan on virustotal or something like that!

I´ll upload the xls to a upload service so you can get it checked before you actually download it to your machine.
I´m saying this because it seems you´re not aware of such risks.

For further protection I can send you the source and you compile it for yourself. Do you want the sourcecode with the binary? It´s written in c#4.0 because I have a DLL for reading xls without office package included.

edit: send me your correct email per U2U and a link to a .txt file (where your clean dataset is).
Then at least you have the xls and can worry about the tool later. If I give you the project, you can use microsofts visual studio free version to run to tool and mess around in the code, I think you´re capable of learning by doing


For further visitors from google, a littly clue:
Use a string datatype (eg char array) and iterate through it using i+=2.
Use second integer var to upcount each iteration to easy get row/column id without running into exceptions later.
Before that, use %2 (modulo) to check for even/uneven and remember to add last digit OUTSIDE of the iteration or you will run into "out of range" errors...

The clue is using string datatype instead of long or unsignet int, it will give you more range.
Protip: Use Spire.XLS for manipulating xls files without office installed...

a reply to: verschickter

U2U and emails sent. I also sent a data sample to you.

I really appreciate your assistance!

I will look forward to your response.

Sorry about the email typo.

Thanks again!

Hey Flyingclaydisk,

As a professional Systems Engineer I agree with ditching Excel to do this work. I would write a program to do it. So I have done just that in Javascript, using JSFiddle online code editor so we can all edit and modify the code if needed.

It includes 3 parts; The HTML part on the top left box, and the Javascript part on the bottom left box, and the final output in the bottom right box.

I added an input textarea which you can supply the PI string. There is an output textarea so you can see the parse result. Also a button so you can continuously parse the numbers in the input textarea on the fly.

On the top of the webpage click the "RUN" button to compile and run the code. From there your focus should be in the bottom right box. Here is the code:

jsfiddle.net...

The output is a Comma Separated Value (CSV) string that I believe you can just copy and "Paste Special" into Excel for viewing.

Enjoy.

-edit to add-
I used your PI string you posted earlier in this topic but it has an asterix in it... so its not accurate. I had to delete it. You may want to double check your source.

a reply to: Flyingclaydisk

Its online, I´ll be back tomorrow...
added the last functions you wanted.

a reply to: WeAre0ne

Thanks!! Appreciate the efforts!

Regarding the data posted, the data itself is intact, but copying the same data from ATS introduces the error. I discovered the same thing and posted it above.

Regarding Excel, yes, I agree it's not the best tool, but it's really handy for quickly morphing through different scenarios in real time.

Thanks

a reply to: verschickter

Outstanding!! I really appreciate it!

Thanks!!

a reply to: Flyingclaydisk

Wait until you see V3

I could not withstand to do it before sleep....
just exchange the v2 in the filename with v3 and reload it from the server....

I think now it´s perfect.
You can adjust how much digits you get and the columns.
It also handles situations when there are 7 digits to split but 4 are left, it will add them in the next line.

I will give it a last touch tomorrow and then I´ll release the source and binary on a public server for everyone else...
If you have any last wishes until then, mail me and I´ll look what I can do. Otherwhise, see my advice on learning C.

a reply to: nullafides

i think nullifides may be right.

that being said....there may be some VB scripting that you could throw into the VB editor to make it work.

this sounds like something fun to play with....ill have a look and see if there may be a solution that you can use excel to find.