Welcome Guest
Log In | Register )
You last visited January 17, 2017, 1:54 am
All times shown are
Eastern Time (GMT-5:00)

Excel Question for you Brainiacs!

Topic closed. 9 replies. Last post 8 years ago by KnuckleHead.

Page 1 of 1
PrintE-mailLink
Rakster's avatar - praying hands.jpg
Saskatchewan
Canada
Member #19992
August 9, 2005
2867 Posts
Offline
Posted: May 3, 2009, 8:54 pm - IP Logged

What formula would I use to divide a 4 digit number (1234) into 4 different columns on a worksheet?

Your help is greatly appreciated!

We are all Lucky... just some of us don't realize it!

    Raven62's avatar - binary
    New Jersey
    United States
    Member #17843
    June 28, 2005
    50995 Posts
    Offline
    Posted: May 3, 2009, 9:10 pm - IP Logged

    Lottery Post Search is YOUR Friend:

    http://www.lotterypost.com/thread/172280/1000196

    It pays to review Previous Threads!

    A mind once stretched by a new idea never returns to its original dimensions!

      Rakster's avatar - praying hands.jpg
      Saskatchewan
      Canada
      Member #19992
      August 9, 2005
      2867 Posts
      Offline
      Posted: May 3, 2009, 9:36 pm - IP Logged

      Lottery Post Search is YOUR Friend:

      http://www.lotterypost.com/thread/172280/1000196

      It pays to review Previous Threads!

      I tried using it...lol  I figured I had asked a similar question a while back but couldn't find it in the results.

      I typed in excel help for formula's or something like that, but it never gave me anything useful.

      Thanks for your help!

      We are all Lucky... just some of us don't realize it!

        Avatar
        Gent
        Belgium
        Member #73987
        April 30, 2009
        33 Posts
        Offline
        Posted: May 4, 2009, 4:11 am - IP Logged

        Hi Rakster, I'm not at all an excel expert, but hopefully I can help you with this:

         

        In cell A1 is your four digit number: 1234

        In cell B1, type the following formula: =INT(A1/1000)

        In cell C1, type the following formula: =INT((A1-B1*1000)/100)

        In cell D1, type the following formula: =INT((A1-B1*1000-C1*100)/10)

        In cell E1, type the following formula: =A1-B1*1000-C1*100-D1*10

         

        As a result, cells A1 to E1 should look like this:

        1234        1          2          3          4

          winsumloosesum's avatar - Lottery-060.jpg
          Pennsylvania
          United States
          Member #2218
          September 1, 2003
          5396 Posts
          Offline
          Posted: May 5, 2009, 12:07 am - IP Logged

          Assuming Cell C is where you enter the 4 digit combo...

          in cell D ...  =INT(C2/1000)

          in cell E ...  =INT((C2-(D2*1000))/100)

          in cell F ...  =INT((C2-((D2*1000)+(E2*100)))/10)

          in cell G ...  =INT((C2-((D2*1000)+(E2*100)+(F2*10))))

            johnph77's avatar - avatar
            CA
            United States
            Member #2987
            December 10, 2003
            832 Posts
            Offline
            Posted: May 9, 2009, 9:50 am - IP Logged

            Alternately, if the 4-digit number is entered into Cell A1 -

            In Cell A2: =LEFT(A1,1)*1
            In Cell A3: =MID(A1,2,1)*1
            In Cell A4: =MID(A1,3,1)*1
            In Cell A5: =RIGHT(A1,1)*1

            The "*1" part of the formula ensures the cell content will be numeric. If text is sufficient, then the "*1" may be omitted.

            Blessed Saint Leibowitz, keep 'em dreamin' down there..... 

            Next week's convention for Psychics and Prognosticators has been cancelled due to unforeseen circumstances.

             =^.^=

              KnuckleHead's avatar - box

              United States
              Member #73037
              April 3, 2009
              147 Posts
              Offline
              Posted: May 11, 2009, 10:05 am - IP Logged

              Good morning all,

              I've been reading/watching this thread with interest.

              Here's my question. Can a formula do the opposite? In other words, if you have 4 cells in a row and you want to tie those numbers into one cell without "summing" them, can that be done without formatting the cell to a "label"?

              Curiously interested...

                        The only DUMB question is the one question you DID NOT ask...

                Avatar
                New Mexico
                United States
                Member #58526
                February 18, 2008
                683 Posts
                Offline
                Posted: May 11, 2009, 10:33 am - IP Logged

                Good morning all,

                I've been reading/watching this thread with interest.

                Here's my question. Can a formula do the opposite? In other words, if you have 4 cells in a row and you want to tie those numbers into one cell without "summing" them, can that be done without formatting the cell to a "label"?

                Curiously interested...

                KnuckleHead, that can be done just by using "&".  Let's say your four numbers are in cells A1,B1,C1,D1........

                the formula in E1       =A1&B1&C1&D1       would return all four numbers together as one

                  Raven62's avatar - binary
                  New Jersey
                  United States
                  Member #17843
                  June 28, 2005
                  50995 Posts
                  Offline
                  Posted: May 11, 2009, 10:48 am - IP Logged

                  Good morning all,

                  I've been reading/watching this thread with interest.

                  Here's my question. Can a formula do the opposite? In other words, if you have 4 cells in a row and you want to tie those numbers into one cell without "summing" them, can that be done without formatting the cell to a "label"?

                  Curiously interested...

                  A2=3, B2=4, C2=5, D2=6 E2=+$A2*1000+$B2*100+$C2*10+$D2*1

                  A mind once stretched by a new idea never returns to its original dimensions!

                    KnuckleHead's avatar - box

                    United States
                    Member #73037
                    April 3, 2009
                    147 Posts
                    Offline
                    Posted: May 11, 2009, 12:59 pm - IP Logged

                    It's not a formula I needed, I just wondered if it could be done. Now everyone who views this "original" post will be able to see how to formulate in both directions. The results for both formulas appear to be very simple.

                    Thank you for the quick response.

                              The only DUMB question is the one question you DID NOT ask...