Welcome Guest
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
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!

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!

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.

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!

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

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

 Page 1 of 1