Counting cell content separated by "/"

[Monkeyboy126]

Hi all,

I have cell content that looks something like "PD/AS/FD/JD". These are actually initials of people working on a project. I need a formula that will count the number of people (or initials) for the cell content, so in the example given it would be four.

All initials are seperated using the "/" character.

Any help greatly appreciated.

Thanks

 

[Monkeyboy126]

Just in case anyones interested, I managed to resolve this by using the following formula (assuming the data is in column D)

=IF(LEN(TRIM(D2))=0,0,LEN(TRIM(D2))-LEN(SUBSTITUTE(D2,"/",""))+1)

Many thanks

 

[SkipVought]

hi,

It the format is ALWAYS and WITHOUT FAIL, "ii/ii/ii/ii", then the logic is...
[tt]
=INT((LEN(A1)+1)/3)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bluedragon2]

=FIND("/",A1)+1

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Gruuuu]

FIND returns the first location of the sought string withing the searched string. bluedragon, your result is coincidentally correct for this example. If the example was "H/GG/J/II" or "H/G" the result would be 2 both times.
Skip's shortcut, coincidentally, would fail those examples as well, but it may not matter, as the OP said they were initials.

Most definitely, the most accurate answer is the one that Monkeyboy came up with, with the one exception of discarding the trims from the final calculation

=IF(LEN(TRIM(A1))=0,0,LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))

is sufficient

 

[bluedragon2]

Gruuu, You are correct, breezed through it to quickly.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...