Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Counting the number of items in a cell

Counting the number of items in a cell

Counting the number of items in a cell

A simple question for you guys, I just don’t know how to enter this in formula. Say in a certain cell A1 containing numbers 1 15 25 4, the formula counts how many numbers contained in that cell A1. The formula should return 4 or in case the cell contains 15 2 8 10 100 59 then it should return 6 and so on. The numbers provided in the cell is either separated by comma or spaces, arranged in any order(ascending or descending). Thank you very much in advance!     

RE: Counting the number of items in a cell

You'll have to parse the cell contents, unless there is a pretty sophisticated string analysis function.

You may have some joy using SEARCH and SUBSTITUTE to look for your delimiters, but you'll have to watch out for double spaces etc.


Greg Locock

RE: Counting the number of items in a cell

A little VBA will get you started. Put a Command button on the page and paste this code into the sheet code page:

Private Sub CommandButton1_Click()
a = ActiveSheet.Cells(1, 1).Value
MsgBox UBound(Split(a, " ")) + 1
End Sub

As Greg says, you will have to remove double spaces first, but this will get you started. You can obviously trigger this code however you want

Good Luck
To get the best from these forums read FAQ731-376 before posting

UK steam enthusiasts: www.essexsteam.co.uk

RE: Counting the number of items in a cell

Try the following Visual Basic Function:

Public Function NumberCount(cell)
  Dim i1 As Integer, i2 As Integer
  i1 = 0
  For Each Elem In Split(cell, " ")
    If Len(Elem) Then i1 = i1 + 1
  i2 = 0
  For Each Elem In Split(cell, ",")
    If Len(Elem) Then i2 = i2 + 1
  If i1 > i2 Then
    NumberCount = i1
    NumberCount = i2
  End If
End Function

It doesn't work if commas and spaces are intermixed: you should make a replace upon entering the function if you have such condition.
The function doesn't check whether the numbers are legitimate as such (in fact it counts the number of words); also I didn't check whether there are error conditions that could cause it to fail (it works for a blank cell though).


Online tools for structural design

RE: Counting the number of items in a cell

Now, without VBA you can substitute the spaces (or the commas) in the string, and then subtract the length of the new string from the length of the original string. For example, if you have in D5: 12 23 34 54 3
and in E5:  =SUBSTITUTE(D5," ","")
which results in 122334543
then =LEN(D5)-LEN(E5)+1 will give you the right answer.
You can of course substitute the formula in E5 into the last formula, and get the answer right away. You can also nest two substitute formulas, one that replaces the commas, one for the spaces.
Have fun!


Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Counting the number of items in a cell

Thank you for your response guys. Though it seems hard, I will try them one by one. I know I am on the right path.

RE: Counting the number of items in a cell

You can parse the cell contents as suggested by GregLock by using Data|Text to Columns.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close