Welcome Guest
Log In | Register )
You last visited December 4, 2016, 11:27 pm
All times shown are
Eastern Time (GMT-5:00)

Question for excel experts

Topic closed. 6 replies. Last post 10 years ago by retxx.

Page 1 of 1
PrintE-mailLink
Mickie's avatar - Lottery-008.jpg
Spring Lake
United States
Member #313
April 26, 2002
181 Posts
Offline
Posted: February 6, 2007, 4:10 pm - IP Logged

Every one is doing, Square roots & Pi's.

My number is 6082 and I did a square root which equals 7798.7178433381
Now I want to make these new numbers:
7798 - 7987 - 9871 - 8717 - 7178 - 1784 - 7843 - 8433 - 4333 - 3381
What I did was to take the first 4# then the next 4#, etc
What formulas would I use to extract these.

Thank you
Mickie

MickieDisney

    Avatar

    United States
    Member #41846
    June 23, 2006
    459 Posts
    Offline
    Posted: February 6, 2007, 5:55 pm - IP Logged

    Mickie

    let me assume your # is in a1   you want your result to be in c1  and you want the 4 # starting at position 5

    in c1 type       =mid(a1,5,4)                 cell a1 start position 5  4 #

     

    enjoy

    p8 

      Mickie's avatar - Lottery-008.jpg
      Spring Lake
      United States
      Member #313
      April 26, 2002
      181 Posts
      Offline
      Posted: February 6, 2007, 7:30 pm - IP Logged

      Thank you!

      You got my mind a flowing. Here is what happened and what I ended up with. Hope others enjoy on their endeavors of the sqrt and PI they are working on.


      Original #
      6082What it gave me
      779877798.71784333817798798.98.78.71.7177178178478438433433333383381
      What I wanted77987987987187177178178478438433433333383381
      7798717843338177987987987187177178178478438433433333383381
      Left7798
      Right7178433381
      Concentrate77987178433381
      NEW=CONCATENATE(LEFT(B4,4),RIGHT(B4,10)) 

      Here is you final formula: =CONCATENATE(LEFT(B4,4),RIGHT(B4,10))

       

      Pk 4
      Original at A3
      =LEFT(B4,4)
      =MID($B4,1,4)  --Repeat across for as many numbers you want--
      =CONCATENATE(LEFT(B4,4),RIGHT(B4,10))


      Pk3
      Original at A3
      =LEFT(B4,3)
      =MID($B5,1,3)  --Repeat across for as many numbers you want--
      =CONCATENATE(LEFT(B4,3),RIGHT(B4,11))


      Original #
      682
      261261.151297144012261615151512129297971714144440401012
      26151297144012
      Left261
      Right51297144012
      Concentrate26151297144012
      NEW=CONCATENATE(LEFT(B4,3),RIGHT(B4,11)) 

       


      Mickie

      MickieDisney

        retxx's avatar - mrthumbs
        BOSTON
        United States
        Member #48
        September 9, 2001
        3584 Posts
        Offline
        Posted: February 7, 2007, 11:05 pm - IP Logged

        hey mickie i am not very good at macros so could you or someone set up the macro in a spreadsheet so I can download it and see ifit works.Thanks

          SirMetro's avatar - center
          East of Atlanta
          United States
          Member #6191
          August 11, 2004
          1389 Posts
          Offline
          Posted: February 8, 2007, 8:51 am - IP Logged

          Here is the Macro that will function IF your primary number is in Cell A1

          =CONCATENATE(LEFT(TEXT((SQRT(A1)*1000000000000),"###############"),4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),2,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),3,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),4,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),5,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),6,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),7,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),8,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),9,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),10,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),11,4))

          To make this work for a Pick 3, change all the 4's to a 3

            Mickie's avatar - Lottery-008.jpg
            Spring Lake
            United States
            Member #313
            April 26, 2002
            181 Posts
            Offline
            Posted: February 8, 2007, 5:49 pm - IP Logged

            SirMetro

             

            Wow!! Thank you!
            There is a small problem though, I did fix one part(I took and made them individual in cells)


            see what the last number is? A 3 digit.

            0027

             

            5196196196156152152452422422422722702707707

             

            Here is the original:
            0027

            5196 - 1961 - 9615 - 6152 - 1524 - 5242 - 2422 - 4227 - 2270 - 2707 - 707

            The purple is the number I want to Sq root.


            Retxx as soon as I finish I will send you a copy.

             

            Mickie

            MickieDisney

              retxx's avatar - mrthumbs
              BOSTON
              United States
              Member #48
              September 9, 2001
              3584 Posts
              Offline
              Posted: February 8, 2007, 10:45 pm - IP Logged

              thanks Mickie,looking forward to good results I hope.