I am trying to identify how many elements have been concatenated.

using =LEN(CONCATENATE(Str1,Str2,Str3)) this gives the total length of whole string. I want 3 as output meaning 3 strings are concatenated, if among the 3 any one is empty then the output should be 2 and similarly if 2 are empty the o/p should be 1 and 0 when all are empty.

I couldn't find how to achieve this.

Any help on any formula or via apps script if we can achieve.

Please help!

CodePudding user response：

UPDATE: to substract the empty values:

=counta(split(A1,",",1,1))-if(ifna(regexmatch(split(A1,",",1,1),",")),1,0)

Try with:

=COUNTA(SPLIT(A1,",",1,1))

I assumed they were separated by commas, but you can change that