Counting names within a cell

[mrhk22]

I have a file exported from a system, with multiple customer names appearing in cells within a column. The format is:

Firstname Lastname | Firstname Lastname | Firstname Lastname | Firstname Lastname | Firstname Lastname |

What formula can I use to count the number of customers in the cell? Many thanks.

 

[Peter_SSs]

Try

=LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[mrhk22]

Try

=LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks for your help

 

[Peter_SSs]

Thanks for your help

You're welcome. Thanks for the follow-up.