+ Reply to Thread
Results 1 to 6 of 6

Breaking out a 3 digit number

  1. #1
    Forum Contributor
    Join Date
    11-13-2010
    Location
    Warren, Michigan
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    307

    Breaking out a 3 digit number

    Earlier today I had a problem with a formula.

    The solution was making sure my original 3 digit input was a number format instead of a text format (why because some numbers that were inputted started with a "0" which a "general" format wouldn't pick up).

    So I added a "000" to my "custom" format and it seems to solve the previous problem.

    Now as I try to break out any "new" inputted numbers as you'll notice in row 20 of my sample file of 052 works but in Columns B, C, D when I try to break out the number individually the "0" is dropped and the last 2 numbers "slide" over?????

    You might say in prior numbers there were "0" in the first position - those number existed, the problem happens with any "NEWLY" inputted numbers.

    B20 coding: =IFERROR(MID($A20,COLUMNS($A$3:A$3),1)+0,"")
    C20 coding: =IFERROR(MID($A20,COLUMNS($A$3:B$3),1)+0,"")
    D20 coding: =IFERROR(MID($A20,COLUMNS($A$3:C$3),1)+0,"")

    See attached sample.

    And any suggestions???
    Attached Files Attached Files
    Last edited by khank; 05-28-2011 at 11:56 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Breaking out a 3 digit number

    Put this formula in B3 and copy down/across the table. I've highlighted the notable change:

    =MID(TEXT($A3,"000"),COLUMNS($A$3:A$3),1)+0


    Then of course, convert the column A values to actual numbers as described in the other thread.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Breaking out a 3 digit number

    Using your posted workbook...
    this regular formula begins parsing the digits out of cell A3
    Please Login or Register  to view this content.

    Copy that formula across and down through cell D20.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Forum Contributor
    Join Date
    11-13-2010
    Location
    Warren, Michigan
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    307

    Re: Breaking out a 3 digit number

    Jerry and Ron both solutions work well. In Excel there's so many ways to "skin-a-cat". Thanks to the both of you!

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Breaking out a 3 digit number

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  6. #6
    Forum Contributor
    Join Date
    11-13-2010
    Location
    Warren, Michigan
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    307

    Re: Breaking out a 3 digit number

    Sorry - done.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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