Email_Count

[Blessy Clara]

Hi

I have an excel sheet with a column of emails - Each row contains multiple emails separated by "||" - I want to calculate the total number of emails in the column - EMAIL - Thanks in Advance

Example as given below

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 871"]
<tbody>[TR]
[TD="class: xl64, width: 871"]goldcoast@flooringxtra.com.au || modern@flooringxtra.com.au || yamba@flooringxtra.com.au || universal@flooringxtra.com.au || simons@flooringxtra.com.au || centralcoast@flooringxtra.com.au || penrith@flooringxtra.com.au || stoltenberg@flooringxtra.com.au || hornsby@flooringxtra.com.au || nulay@flooringxtra.com.au || finley@flooringxtra.com.au || orana@flooringxtra.com.au || absolute@flooringxtra.com.au || batemansbay@flooringxtra.com.au || bunbury@flooringxtra.com.au || willetton@flooringxtra.com.au || rockingham@flooringxtra.com.au || oconnor@flooringxtra.com.au || Kelmscott@flooringxtra.com.au || oceankeys@flooringxtra.com.au || canningvale@flooringxtra.com.au || park@flooringxtra.com.au || keilor@flooringxtra.com.au || finesse@flooringxtra.com.au || richriver@flooringxtra.com.au || mildura@flooringxtra.com.au || hoppers@flooringxtra.com.au || westernport@flooringxtra.com.au || hamilton@flooringxtra.com.au || kerryn.scanlan@flooringxtra.com.au || bendigo@flooringxtra.com.au || peninsula@flooringxtra.com.au || southadelaide@flooringxtra.com.au || adelaidecity@flooringxtra.com.au || gladstone@flooringxtra.com.au || bayside@flooringxtra.com.au || coastal@flooringxtra.com.au || specialty@flooringxtra.com.au || style@flooringxtra.com.au || brownsplains@flooringxtra.com.au || topfit@flooringxtra.com.au || corowa@flooringxtra.com.au || hawkesbury@flooringxtra.com.au || naturallyfloored@flooringxtra.com.au || nowra@flooringxtra.com.au || carpetculture@flooringxtra.com.au || cooma@flooringxtra.com.au || southsydney@flooringxtra.com.au || sommers@flooringxtra.com.au || shop@flooringxtra.com.au || info@flooringxtra.com.au || career@flooringxtra.com.au[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 871"]
<tbody>[TR]
[TD="class: xl65, width: 871"]hello@friendliespharmacies.com.au || fluvac@friendliespharmacies.com.au[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 871"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, maybe you could try:

=SUMPRODUCT(LEN(B2:B100)-LEN(SUBSTITUTE(B2:B100,"@","")))

 

[Blessy Clara]

Hi FormR

Thank you for your response and the formula - Yeah that is cool