+ Reply to Thread
Results 1 to 7 of 7

Extract each digit from 3 Digit Numbers and put in other cells?

  1. #1
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    173

    Extract each digit from 3 Digit Numbers and put in other cells?

    Hey All,
    I need help with a new formula, I have been using this Data for a while, but I put it in manually and that is getting old.

    Im hoping that I explain this correctly so people will understand, I apologize if it's confusing. I will do my best to explain.

    The Notes in the attachment should help with understanding my goal for the formula.

    Ok, here it is.

    I have 3 digit numbers 0-9 that will continue down Column(D) Examples are 432, 287, 165, 526, 580, 613, 631, etc.

    These 3 digit numbers I will need to take and extract each individual digit and put in a row, the columns will be E-N and (labeled 0-9 in row 1)

    I will need the formula to look at Seven of the 3 digit numbers at a time, it will count each individual digit and put in the row of the columns marked 0-9

    Example from numbers above would go in Columns E-N marked 0-9 in row 8.
    Note:
    Column E is Marked "0"
    Column F is Marked "1"
    Column G is Marked "2" and so on until Column N which is Marked "9"

    Below is how it works:

    432, 287, 165, 526, 580, 613, 631

    This would be: E8 is 1 because only one "0" is in the seven 3 digit numbers listed above
    F8 is 3 because of three "1's" in the seven 3 digit numbers
    G8 is 3 because of three "2's" in the seven 3 digit numbers.
    and so on until
    N9 which is "0" because of no "9's" in the seven 3 digit numbers

    If you add the Row 8 Columns E-N together you will get 21 which is the seven 3 digit numbers (7 times 3 = 21)

    Once a new 3 digit number arrives after the 631 from numbers above i will need to take away the first number which is (432) from the formula (But leave it in place on my excel sheet)

    So the new seven 3 digit numbers will be 287, 165, 526, 580, 613, 631, 355

    Row 9 will show just like the above example except leaving the 432 out and adding the 355

    This same pattern will continue

    I have faith that this forum and the experts will figure this out for me.

    I'm learning more and more everyday with formulas in excel. I used excel for years putting in numbers manually not knowing anything about formulas until recently and it has changed my world.

    Thanks in advance,
    Brian
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Extract each digit from 3 Digit Numbers and put in other cells?

    Hi Brian,

    Find your answer attached.

    Add and Subtract Digits to Cells answer.xlsx

    The "trick" is to treat each of your numbers in Col D as text. Concatenate the numbers above and then substitute the heading number with blank. Count the original length and the result of removing the number to find out how many there were. The Dollar Signs are of most importance in this problem.

    Hope this is what you wanted.
    Last edited by MarvinP; 05-21-2017 at 01:16 AM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract each digit from 3 Digit Numbers and put in other cells?

    Try this in E8:N21

    =SUM(MMULT(--(--MID($D2:$D8,{1,2,3},1)=E$1),{1;1;1}))

    It returns the same as the expected numbers. Also each row sum = 21.

    I am afraid I do not understand what you want to do in the remaining instructions.
    Dave

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract each digit from 3 Digit Numbers and put in other cells?

    This is a simpler formula than my previous in post#3.

    =SUMPRODUCT(--(--MID($D2:$D8,{1,2,3},1)=E$1))

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract each digit from 3 Digit Numbers and put in other cells?

    Another way.

    =SUMPRODUCT(COUNTIF(E$1,MID($D2:$D8,{1,2,3},1)))

  6. #6
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    173

    Re: Extract each digit from 3 Digit Numbers and put in other cells?

    I want to say thank you to MarvinP and FlameRetired for responding and working on my formula for me. Both of you did a great job with no issues.

    Thanks Again,
    Brian

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract each digit from 3 Digit Numbers and put in other cells?

    You're welcome. Thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to identify 3-digit and 4-digit numbers as valid dates
    By gojakie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-16-2015, 08:16 AM
  2. [SOLVED] LOTTERY FILTER#4, Find if 1 Digit Sum of 2 Digit or 3 Digit, Single Cell w/ dash
    By david gonzalez in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-08-2014, 12:57 AM
  3. Extract 4 digit common numbers from 5 digit numbers
    By ameque in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-24-2013, 01:09 AM
  4. Replace all one-digit number with two-digit numbers
    By sandykunaish in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2013, 09:56 AM
  5. [SOLVED] Extract 10 digit numbers from other text
    By mellopete in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-09-2012, 05:21 PM
  6. [SOLVED] macro to extract 9 digit numbers as well as alpha numberic 9 digit numbers from txt file
    By Raju Radhakrishnan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-13-2012, 10:15 AM
  7. To count numbers with 4 digit/5 digit with given Range.
    By ebin charles in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2012, 08:49 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1