Counting Certain Characters in a Cell with Nested IF Function

[XL1980]

Hello,

I have the following data in cells:

1
1.12.1.1.2
1.12.1.1.3
1.12.1.1.4
1.12.1.1.4.8
1.12.1.1.5
1.12.1.1.6
1.12.1.1.7
1.12.1.1.8
1.12.1.1.9
1.12.1.1.9.9
1.12.1.1.10
1.12.1.1.10.8
1.12.1.2
1.12.2
1.12.2.1
1.12.3
1.12.3.1
1.12.3.1.1.4
1.12.3.1.1.10
1.12.3.1.2.4
1.12.3.1.3
1.12.3.1.3.1
1.12.3.1.3.4
1.12.3.1.3.7
1.12.3.1.4
1.12.3.1.4.4

I need to write a nested IF formula where the adjacent cells bring back a certain value based on the number of “.” (fullstops) in the cell.

I have tried =IF(LEN(A1)…. But does not give me the desired results for all cells.

Any assistance would be greatly appreciated.

Thank you.

 

[Joe4]

You can use this formula to count the number of full stops in each value:

=LEN(A1)-LEN(SUBSTITUTE(A1,".",""))

 

[XL1980]

Hi Joe4,

Thank you for your reply. Ideally, I want to build the formula you have provided in to a nested IF statement. Any ideas?

 

[Joe4]

Thank you for your reply. Ideally, I want to build the formula you have provided in to a nested IF statement. Any ideas?

You should be able to insert/imbed it anywhere in your IF function where you want to return the number of full stops.
If you are having issues incorporating that, you will need to tell us exactly what you want your IF function to do.

 

[XL1980]

Joe4,

I tried the following which didn't work:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"."=1,"LEVEL 1" etc.........

I want an IF statement to say:

If one fullstop, then return a value of "LEVEL 1", If two fullstops, then return a value of "LEVEL 2" etc.....

Many thanks.

 

[Joe4]

The "=1" part needs to come after the formula I wrote (not imbedded in it). That part of the function is comparing the length of the string to its length if we remove all the full stops (the difference is the number of full stops we have). What you did tells the function to find the length after replacing all the full stops with "=1". Definitely NOT what you want to do.

It should start:

[COLOR=#333333]=IF([/COLOR][COLOR=#333333]LEN(A1)-LEN(SUBSTITUTE(A1,".",""))=1[/COLOR][COLOR=#333333],"LEVEL 1" etc.........[/COLOR]

 

[XL1980]

Thanks Joe4. Works perfectly!