Extracting level 'N' all the way down

[DashboardNovice]

In the attached file, I have project ID numbers which exist at various levels. The project IDs contain a dot to separate each level.

So we have:

• # of Dots = Level # -1
• Level # = # of Dots + 1

For each example, I want to extract level 1, 2, 3 and 4. When I extract any given level #, I want to extract it for that level and below. For example, if I want to extract level 2, I want to extract it at levels 2, 3 and 4 like F16:F18. However, I am having trouble extracting any level at that same level (see G16). This has to do with the fact that the # of dots at any given level is = Level # -1.

The orange cells show the results I am trying to obtain. The yellow cells show my formulas.

One last thing to point out is that while level 1 will always have 5 characters, the number of characters at level 2, 3 and 4 may vary. I included example #2 to show this.

So I am trying to determine a formula that can work in all of these situations in yellow in columns G and N.

Can anybody offer any suggestions on how to extract any given level (e.g. level 2) across all project IDs at that level and below (e.g. levels 2, 3 and 4).

 

[Peter Bartholomew]

This is for 365.

= IFNA(
    TEXTBEFORE(projectID,".",level,,1),
  "")

 

[DashboardNovice]

This is for 365.

= IFNA(
    TEXTBEFORE(projectID,".",level,,1),
  "")

Thank you.

 

[DashboardNovice]

I have modified my original file in this attached file. I am trying to obtain the results in the orange column. The yellow column is where my formula is. I'm having trouble with how to handle these #N/A errors.

For example, take the table starting on row 64 that extracts the 3rd level. I thought about using IFNA( ) then copying the value from the cell above. That will work for any level 4 project IDs, but not for levels 1 and 2.

For the table that extracts level 2, using IFNA( ) then copying from the cell above would work, except for cell G35 since that is level 1.

I can using this IFNA( ) idea for the first table that extracts level 1, but I need a formula that will be consistent when extracting all 4 levels.

To be more selective, for any given row, I tried using the IFNA( ) idea, only if the level # for that row is > the level # for the cell below it. This works only in some cases, e.g. G37, but not in all cases, e.g. G38.

The problem I'm running into is you can have a lower level project ID following a project ID at a higher level, e.g. D43 following D42.

 

[Peter Bartholomew]

Ninjas
Could someone else help here.
I can make progress on the problem by using 365, but that is of little value to the OP if they do not have access to 365. I haven't used VLOOKUP or direct cell referencing for years so we have a mismatch.