# Breaking out a 3 digit number

1. ## 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???

2. ## 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.

3. ## Re: Breaking out a 3 digit number

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?

4. ## 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. ## 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. ## Re: Breaking out a 3 digit number

Sorry - done.

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

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