SOLVED

is there Formula to find the number of values inside the cell?

%3CLINGO-SUB%20id%3D%22lingo-sub-2763645%22%20slang%3D%22en-US%22%3Eis%20there%20Formula%20to%20find%20the%20number%20of%20values%20inside%20the%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2763645%22%20slang%3D%22en-US%22%3E%3CP%3Ehey%20everybody%3CBR%20%2F%3Ecan%20someone%20help%20me%20with%20Formula%20or%20function%20to%20find%20the%20number%20of%20values%20inside%20the%20cell%3C%2FP%3E%3CP%3Elike%20the%20example%20below%26nbsp%3B%3CBR%20%2F%3Enote%5C%3CBR%20%2F%3Ems%20excel%202016%3CBR%20%2F%3Ewindows%2010%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22ex.PNG%22%20style%3D%22width%3A%20569px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311300i3176ED7D82864D04%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22ex.PNG%22%20alt%3D%22ex.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2763645%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2763718%22%20slang%3D%22en-US%22%3ERe%3A%20is%20there%20Formula%20to%20find%20the%20number%20of%20values%20inside%20the%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2763718%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1159899%22%20target%3D%22_blank%22%3E%40AdamAli93%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D(LEN(A1)-LEN(SUBSTITUTE(A1%2C%22xx%22%2C%22%22)))%2F2%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2763724%22%20slang%3D%22en-US%22%3ERe%3A%20is%20there%20Formula%20to%20find%20the%20number%20of%20values%20inside%20the%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2763724%22%20slang%3D%22en-US%22%3Ethanks%2C%20it%20works%20perfectly%20%3Arose%3A%3Atulip%3A%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2763839%22%20slang%3D%22en-US%22%3ERe%3A%20is%20there%20Formula%20to%20find%20the%20number%20of%20values%20inside%20the%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2763839%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1159899%22%20target%3D%22_blank%22%3E%40AdamAli93%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20not%20suggesting%20that%20you%20need%20to%20do%20this%20but%2C%20out%20of%20curiosity%2C%20I%20tried%20Charles%20Williams's%20Fast%20Excel%20add-in%20which%20has%20functions%20to%20process%20regular%20expressions.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20796px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311314i76D0E85B33D0854E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHaving%20extracted%20the%20strings%2C%20I%20simply%20counted%20them%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20Rgx.MID(%40cellValue%2C%20%22H%2F%5Cd*%22%2C0)%0A%0A%3D%20COUNTA(Rgx.MID(%40cellValue%2C%20%22H%2F%5Cd*%22%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThe%20regular%20expression%20was%20set%20the%20return%20any%20string%20that%20starts%20with%20%22H%2F%22%20and%20continues%20with%20an%20arbitrary%20number%20of%20digits.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2764242%22%20slang%3D%22en-US%22%3ERe%3A%20is%20there%20Formula%20to%20find%20the%20number%20of%20values%20inside%20the%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2764242%22%20slang%3D%22en-US%22%3Ethanks%2C%20it%20can%20help%20me%20in%20another%20project%20%3Abouquet%3A%3C%2FLINGO-BODY%3E
New Contributor

hey everybody
can someone help me with Formula or function to find the number of values inside the cell

like the example below 
note\
ms excel 2016
windows 10ex.PNG

 

4 Replies
best response confirmed by AdamAli93 (New Contributor)
Solution

@AdamAli93 

=(LEN(A1)-LEN(SUBSTITUTE(A1,"xx","")))/2

 

thanks, it works perfectly

@AdamAli93 

I am not suggesting that you need to do this but, out of curiosity, I tried Charles Williams's Fast Excel add-in which has functions to process regular expressions.

image.png

Having extracted the strings, I simply counted them

= Rgx.MID(@cellValue, "H/\d*",0)

= COUNTA(Rgx.MID(@cellValue, "H/\d*",0))

The regular expression was set the return any string that starts with "H/" and continues with an arbitrary number of digits.

thanks, it can help me in another project