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

Another Excel Question...

Topic closed. 6 replies. Last post 6 years ago by winsumloosesum.

Page 1 of 1
PrintE-mailLink
Rakster's avatar - praying hands.jpg
Saskatchewan
Canada
Member #19992
August 9, 2005
2867 Posts
Offline
Posted: July 27, 2010, 5:45 pm - IP Logged

If I have the number 7412 in cell "J3"

How do I formulate it so I can get the 7 in one cell, the 4 in another cell the 2 in another cell and the 1 in another cell?

These formulas get me the first 3 digits into seperate columns but how do I get the 4th digit...anyone know the formula for this one?

Thanks

 =IF(ISBLANK(J3),"",INT(J3/100))

=IF(ISBLANK(J3),"",INT((J3-(K3*100))/10))

=IF(ISBLANK(J3),"",INT((J3-((K3*100)+L3*10))))

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

    Avatar
    Iron Station, NC
    United States
    Member #74828
    May 21, 2009
    69 Posts
    Offline
    Posted: July 27, 2010, 7:25 pm - IP Logged

    Hi-Lite the cell the numbers are in, click on Data, then Text to Columns, click the Fixed width , then next, click on to creat a breaking line click

    at the desired position, (place a line between each didgit), then click finsh.

    Hope this works for you

      Avatar

      United States
      Member #41846
      June 23, 2006
      460 Posts
      Offline
      Posted: July 27, 2010, 9:37 pm - IP Logged

      I assume you are looking for a formula

      several ways to do this. my favorite is =mid(j3,1,1) for the first # then =mid(j3,2,1)  etc.

        j3 is obviously cell reference. next digit defines the position you want to start, then 3rd # tells it how many #'s to select. by using 1 i get 1 number

        Raven62's avatar - binary
        New Jersey
        United States
        Member #17843
        June 28, 2005
        51064 Posts
        Offline
        Posted: July 27, 2010, 11:58 pm - IP Logged

        If I have the number 7412 in cell "J3"

        How do I formulate it so I can get the 7 in one cell, the 4 in another cell the 2 in another cell and the 1 in another cell?

        These formulas get me the first 3 digits into seperate columns but how do I get the 4th digit...anyone know the formula for this one?

        Thanks

         =IF(ISBLANK(J3),"",INT(J3/100))

        =IF(ISBLANK(J3),"",INT((J3-(K3*100))/10))

        =IF(ISBLANK(J3),"",INT((J3-((K3*100)+L3*10))))

        Lottery Post Forums Search is Your Friend:

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

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

          Avatar
          Iron Station, NC
          United States
          Member #74828
          May 21, 2009
          69 Posts
          Offline
          Posted: July 28, 2010, 8:28 am - IP Logged

          You will not have to use a formula if you use the Data to Column, and you can do a whole column of numbers.  Work with it

          and see how easy it is.

            Rakster's avatar - praying hands.jpg
            Saskatchewan
            Canada
            Member #19992
            August 9, 2005
            2867 Posts
            Offline
            Posted: July 30, 2010, 1:21 am - IP Logged

            You will not have to use a formula if you use the Data to Column, and you can do a whole column of numbers.  Work with it

            and see how easy it is.

            Thanks guys...

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

              winsumloosesum's avatar - Lottery-060.jpg
              Pennsylvania
              United States
              Member #2218
              September 1, 2003
              5396 Posts
              Offline
              Posted: July 30, 2010, 8:38 pm - IP Logged

              If I have the number 7412 in cell "J3"

              How do I formulate it so I can get the 7 in one cell, the 4 in another cell the 2 in another cell and the 1 in another cell?

              These formulas get me the first 3 digits into seperate columns but how do I get the 4th digit...anyone know the formula for this one?

              Thanks

               =IF(ISBLANK(J3),"",INT(J3/100))

              =IF(ISBLANK(J3),"",INT((J3-(K3*100))/10))

              =IF(ISBLANK(J3),"",INT((J3-((K3*100)+L3*10))))

              7412 (In Cell J3)
              =INT(J3/1000)                                                ' Formula in cell K3
              =INT((J3-(K3*1000))/100)                              ' Formula in cell L3
              =INT((J3-((K3*1000)+(L3*100)))/10)            ' Formula in cell M3
              =INT((J3-((K3*1000)+(L3*100)+(M3*10))))  ' Formula in cell N3