Multiple IF/Then assignments

Ok so what I need to do is set up an If/then macro that will populate anywhere from 1-11 columns based on some previous calcuations done. I'm having trouble with the formatting though.

Im starting out with a number like 1001001164532, this is a Concatenated number that basically holds all the information about a person that I need tracked. Now these numbers can vary in length adding or subtracting at most 5 digits all taken from the right side and moving inward.

So ill have my 11 max columns (number above is one involving 6 columns needing to be populated.) I was going to start with a simple comparison of that number vs the baseline amount of digits so is 1001001164532 greater than 1000000000000, if it is then a formula to extract the specific digits I need. something like (MID($P2,4,1)&LEFT($P2,3))*1
If it failed the test then I want the cells to be blank.

Firstly I'd like to know if there is a command that will just count digits so I can say If X has so many digits then do the formula or make it blank?

Second I can't seem to get the formatting down to where it will actually populate the entire range of cells. I've tried Sheet4.CellsE:E but that does not work, I can't seem to find the proper format anywhere. Any help would be appreciated.

I think you need to post an example.

OK here is an example of what I'm trying to get it to look like. The ID number is the Conc. number I was talking about. Those last digits, the 164532 dictates how many columns there will be. That particular person has 6 calculations because of that. The next person would only have 5. What I want to do is set up an If/then statement that would check to see how many digits the ID has and based on that populate the correct columns with the formula I used in that example sheet. The max ends up being 11 seperate calculations and the minimum is one.

So it'd be like
If column A has X digits then column B = that formula else column B is blank

and of course i'd make one for each of the different parameters, I just need to know the formatting that will make it actually fill out those columns.

In B2 and copied down and right,

=IF(LEN($A2) >= COLUMNS($A2:B2) + 6, MID($A2, COLUMNS($A2:B2) + 6, 1) & LEFT($A2, 7), "")

Well, I put that into the sheet and applied it to those 11 columns, it works for all the numbers already there but when I tested by adding somone who had 8 choices vs the 6, it did not populate the two columns beyond the 6th. I can't really comment more than that because im not even 100% sure what everything in that formula was doing

Thank you though.

If the ID numbers go beyond 15 or 16 digits, they need to be stored as text. That's the limit of precision for IEEE Doubles.

Format the ID cells as text, and enter strings as long as you wish.

Ok, so I redid the formatting for the column it was drawing from to text. However it didnt seem to remedy the problem unfortunately. for some reason once I get to the 9th column to go into the conc formula it seems to turn all of those numbers to 0 rather than 9 10 and 11. Because of that when it goes through the calcuations those numbers also come out with 0 at the front instead of 9 10 or 11. Will I have to edit that formula to get it to take both digits of 10 and 11 into account on the later columns as well?

You've still got it stored as a number; that's why the ending digits are all zero.

1. Format A2 as Text
2. Enter a numeric (or alphanumeric) string as long as you wish.

Right, I've formatted them as text. That puts them into the scientific form x.xxxx e^19 and so on. It doesnt change the values in the calculated cells when I do that. They still show up with 0 as the starting number.

OK, now that the cell is formatted as text, reenter the string. To persuade yourself (and me) that it's text, enter a 20-digit number not ending in 0 and verify that that is what appears.

That gets them to populate somewhat correctly but still kind of off by a digit or so when it gets into 10 and 11.The cell these are drawing the information from is a sorted list of a set of numbers Concatenated from many different variables. Will sorting still even work if they are kept as text vs kept as numbers? There could be anywhere from 1-10,000 inputs into this so manually redoing it all one at a time is pretty much out of the question.

That gets them to populate somewhat correctly but still kind of off by a digit or so when it gets into 10 and 11

"Somewhat correctly"?

Can you post an example that illustrates the problem you're having?

Ok, if I replace one of the numbers with say
10010061645327891011

It would be an example of a person who had all 11 options to choose from, so I need a cell that populates the number for each one, using the formula you gave me the 11 output numbers are:

11001006
61001006
41001006
51001006
31001006
21001006
71001006
81001006
91001006 It did take care of the 9 not showing up correctly though
11001006 <--- should be 1 digit longer than the others, 101001006
1001006 <----- should be 111001006

So aside from that there is also the issue that I cannot manually redo every single number like this, there are just far too many and it wont always be 11 options, some might be 4 some might be 7, it really just depends. I need people to be able to input their data on the main page and it gets sorted automatically on the second. I'd post the sheet itself but at 30+ mb it's a tad large for posting here.

You need to restate the rule.

My rule was (is), "Prefix the first seven characters of the string with each character from the 8th to the end."

Your string is 1001006 1645327891011 (with a space to show the first 7 characters). Notice that the 1 is the character that follows the 9, and is the next prefix in the sequence that the formula generates.

How could one know that the "10" is to be interpreted as a two-digit number??