Separate P3 & P4 Combinations Into Individual Digits In ExcelPrev TopicNext Topic

• Raven62

  

  25

  New Jersey
  United States
  Member #17,842
  June 28, 2005
  180,983 Posts
  Offline
  Apr 6, 2015, 2:03 pm

  ▲▼

  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!
  
  Catch-22: A dilemma or difficult circumstance from which there is no escape because of mutually conflicting or dependent conditions.
  
  Corruptissima re publica plurimae leges: When the republic is at its most corrupt the laws are most numerous.
  
  The best way to learn is to never stop being an Experiential Student!
• Raven62

  

  25

  New Jersey
  United States
  Member #17,842
  June 28, 2005
  180,983 Posts
  Offline
  Apr 6, 2015, 2:08 pm

  ▲▼

  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!
  
  Catch-22: A dilemma or difficult circumstance from which there is no escape because of mutually conflicting or dependent conditions.
  
  Corruptissima re publica plurimae leges: When the republic is at its most corrupt the laws are most numerous.
  
  The best way to learn is to never stop being an Experiential Student!
• GASMETERGUY

  

  NASHVILLE, TENN
  United States
  Member #33,371
  February 20, 2006
  1,044 Posts
  Offline
  Apr 6, 2015, 3:23 pm

  ▲▼

  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

  

  25

  New Jersey
  United States
  Member #17,842
  June 28, 2005
  180,983 Posts
  Offline
  Apr 8, 2015, 1:32 pm

  ▲▼
  In response to GASMETERGUY

  Quote: Originally posted by GASMETERGUY on Apr 6, 2015

  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!
  
  Catch-22: A dilemma or difficult circumstance from which there is no escape because of mutually conflicting or dependent conditions.
  
  Corruptissima re publica plurimae leges: When the republic is at its most corrupt the laws are most numerous.
  
  The best way to learn is to never stop being an Experiential Student!
• Technut

  

  3rd Rock from Sun
  United States
  Member #159,096
  September 13, 2014
  178 Posts
  Offline
  Apr 30, 2015, 11:12 pm

  ▲▼

  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
• ithastobesaid

  

  United States
  Member #123,194
  February 15, 2012
  54 Posts
  Offline
  May 1, 2015, 10:12 am

  ▲▼

  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))