Count number of names in a cell

[mrkris1982]

I2 has 3 names in it...cell looks like this....

Bob Jones
Doug Smith
Chase Williams

I'd like to get J2 to show the number 3 - reflecting the number of names in I2. Is there a way to do this?

 

[gaz_chops]

Maybe

=IF(A1="","",LEN(A1)-LEN(CLEAN(A1))+1)

 

[mrkris1982]

It's only returning odd values (1,3,5,7, etc...)

I did adjust the "A1" to be "I1" but the rest is exactly as suggested


The issue in my case might be that I have a dropdown list in column I with some VBA code that allows me to select a person....and they are added to the cell.....select another person and they are added to the cell on a new line.

When I try your formula on a cell where I manually enter a name, make a new line and enter a new name....the formula works. Think there is a way to make it work for the dropdown list where im selecting multiple names?

 

[gaz_chops]

Hopefully someone more versed in VBA will be along to help.

 

[Fluff]

Can you post the code you are using

 

[mrkris1982]

Private Sub Worksheet_Change(ByVal Target As Range)'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$I$2" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & vbNewLine & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

 

[Fluff]

In that case try
=IF(A1="","",LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1)

 

[mrkris1982]

The value returned just sits at 1 no matter how many names i have in column I

 

[Fluff]

This is what I get

Excel 2013/2016
	I	J
5	Fluff mrkris1982 MrExcel	3
6	Fluff	1
7	Fluff Me	2
Color

Cell Formulas
Range	Formula
J5	=IF(I5="","",LEN(I5)-LEN(SUBSTITUTE(I5,CHAR(10),""))+1)

 

[mrkris1982]

brain fart on my end - this works. I failed to adjust the row along with the column. I needed I2 and was using I1

Thanks!