Excel Magic Trick 289: LEN & SUBSTITUTE functions Counting Tricks

[raja10pk]

kindly give me above worksheet in Excel.

[ExcelIsFun]

Search for and watch this video title:

excelisfun Search & Find Excel Videos, Playlists, Download Excel Workbooks

[dukeofscouts]

How was the last count 5? I count 8 numbers.

Also can you use the AND function with a substitute?

[ExcelIsFun]

Dear dukeofscouts,

The logic of all these tricks is to find some pattern to exploit. We found commas as our pattern.

The 5 numbers that are not zero are: 5 and 658 and 9 87 and 1.

To count the individual characters that are numbers, use this:

LEN(SUBSTITUTE(SUBSTITUTE(B19,­",0",""),",","")

What are you trying to accomplish with the AND and SUBSTITUTE?

--excelsifun