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

Separate P3 & P4 Combinations Into Individual Digits In Excel

Topic closed. 5 replies. Last post 2 years ago by ithastobesaid.

Page 1 of 1
41
PrintE-mailLink
Raven62's avatar - binary
New Jersey
United States
Member #17843
June 28, 2005
51010 Posts
Online
Posted: April 6, 2015, 2:03 pm - IP Logged

Pick 3:

Cell A2=123

Cell B2=> =(MOD($A2,1000)-MOD($A2,100))/100

Cell C2=> =(MOD($A2,100)-MOD($A2,10))/10

Cell D2=> =MOD($A2,10)

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

    Raven62's avatar - binary
    New Jersey
    United States
    Member #17843
    June 28, 2005
    51010 Posts
    Online
    Posted: April 6, 2015, 2:08 pm - IP Logged

    Pick 4:

    Cell A2=1234

    Cell B2=> =(MOD($A2,10000)-MOD($A2,1000))/1000

    Cell C2=> =(MOD($A2,1000)-MOD($A2,100))/100

    Cell D2=> =(MOD($A2,100)-MOD($A2,10))/10

    Cell E2=> =MOD($A2,10)

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

      Avatar
      NASHVILLE, TENN
      United States
      Member #33372
      February 20, 2006
      1044 Posts
      Offline
      Posted: April 6, 2015, 3:23 pm - IP Logged

      One should always put individual numbers into a separate cell.  This way one can manipulate the data with Visual Basic.  It makes for finding patterns that much easier.

        Raven62's avatar - binary
        New Jersey
        United States
        Member #17843
        June 28, 2005
        51010 Posts
        Online
        Posted: April 8, 2015, 1:32 pm - IP Logged

        One should always put individual numbers into a separate cell.  This way one can manipulate the data with Visual Basic.  It makes for finding patterns that much easier.

        Couldn't agree more! However, when faced with a Large Quantity of Three or Four Digit Combinations it's easier to let Excel separate them into Individual Digits.

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

          Technut's avatar - moon
          3rd Rock from Sun
          United States
          Member #159103
          September 13, 2014
          151 Posts
          Offline
          Posted: April 30, 2015, 11:12 pm - IP Logged

          There is another way to split the data apart but it also depends on how you have the data to begin with. What i am reffering to is CSV or Comma Seperated Value file format. The data i have for use in one of the programs i use is in this format. example (MM,DD,YYYYY,B1,B2,B3) if i load this into excel correctly it will split the data into separate cells. I have used this method before for breakdown in the past for splitting purposes.

          Yesterday is History, Tomorrow is a Mystery, Today is a gift that's why it's called the PRESENT! (c8

            Avatar

            United States
            Member #123200
            February 15, 2012
            53 Posts
            Offline
            Posted: May 1, 2015, 10:12 am - IP Logged

            This may be simpler for you:

            Cell A2 = 123

            Cell B2 => =int(left(text(A2,"000"),1))

            Cell C2 => =int(mid(text(A2,"000"),2,1))

            Cell D2 => =int(right(text(A2,"000"),1))