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

need help with excel

Topic closed. 5 replies. Last post 8 years ago by Mickie.

Page 1 of 1
PrintE-mailLink
Avatar
Mount Morris
United States
Member #46750
September 27, 2006
139 Posts
Offline
Posted: March 26, 2009, 7:57 pm - IP Logged

1: I need to see how to make formula for one cell  like   25= 7    63=9       64=0

  2:  i want to break done a string of numbers  like 2698742563.2 but i only want some of them say 8 ,2,6

and the string is in one cell but need to put my pick in another cell for testing faster

    Curious2k3's avatar - Curious 2k3.jpg
    Michigan
    United States
    Member #34209
    March 1, 2006
    265 Posts
    Offline
    Posted: March 26, 2009, 10:14 pm - IP Logged

    Try the following, breaking each number apart.

    SUM B1 and C1

      Mickie's avatar - Lottery-008.jpg
      Spring Lake
      United States
      Member #313
      April 26, 2002
      181 Posts
      Offline
      Posted: March 27, 2009, 9:57 am - IP Logged

      Say your number is 2698742563.2
      Put it in A1, then in A2 put in this formula:

      =MID($A$1,1,1) and it will give you "2"

      $A$1 is the cell your long number is
      ,1 is the position you want extracting (1st digit)
      ,1 is how many digits you want extracted.

       

      For example:  =MID($A$1,1,3) you would get "269"
      =MID($A$1,3,3) you would get "987"

      Hope this helps
      Mickie

      MickieDisney

        Avatar
        Mount Morris
        United States
        Member #46750
        September 27, 2006
        139 Posts
        Offline
        Posted: March 27, 2009, 10:20 am - IP Logged

        Mickie  that formula you posted works for me . Thanks

        now see what curious2k3  posted it want work for me .

        problem  ---  2479=22 sum == sum-4 iam trying to do all automatic with out typing in each digit to break it down using lottery math so when I get my xl done i can let people use it to see how it works in there state. lot of older people don't know where to put the numbers in

        type in 4 digit  2479 = 4 may not be able to do it these way.???

          denn0n's avatar - me small200_
          New Member

          Malaysia
          Member #72778
          March 27, 2009
          9 Posts
          Offline
          Posted: March 27, 2009, 12:30 pm - IP Logged

          Mickie  that formula you posted works for me . Thanks

          now see what curious2k3  posted it want work for me .

          problem  ---  2479=22 sum == sum-4 iam trying to do all automatic with out typing in each digit to break it down using lottery math so when I get my xl done i can let people use it to see how it works in there state. lot of older people don't know where to put the numbers in

          type in 4 digit  2479 = 4 may not be able to do it these way.???

          hey hillbilly953,

          1. If H2 Cell contains 2470
          2. Format H2 as Text

          3. Key in this formula in I2
          =IF(LEN(H2=4);SUM(VALUE(MID(H2;1;1));VALUE(MID(H2;2;1));VALUE(MID(H2;3;1));VALUE(MID(H2;4;1)));SUM(VALUE(MID(H5;1;1));VALUE(MID(H5;2;1));VALUE(MID(H5;3;1))))

          (I am using Open Office)

          4. Result should get in I2 is 13
          5. You could have another column doing 1+3=4 say as Cell J2
          IF(LEN(i2=2);SUM(VALUE(MID(I2;1;1));VALUE(MID(i2;2;1)));SUM(VALUE(MID(i2;1;1));VALUE(MID(i2;2;1));VALUE(MID(i2;3;1)))

          hope that helps.

           

          dennon.

            Mickie's avatar - Lottery-008.jpg
            Spring Lake
            United States
            Member #313
            April 26, 2002
            181 Posts
            Offline
            Posted: March 28, 2009, 8:32 am - IP Logged

            Easy:

             

            A1  2479

            B1 =MID($A1,1,1)+MID($A1,2,1)+MID($A1,3,1)+MID($A1,4,1)

            C1 =MOD(SUM(MID($B1,1,1)+MID($B1,2,1)),10)

             

            A1             B1            C1

            2479224

            or send me your sheet, I will fix it the way you want. I have office 2003.

            Have a good day
            Mickie

            MickieDisney