Results 1 to 3 of 3
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count Commas (Excel XP)

    I have a text file that contains 10ish records - the first record is a header record. Another program (not MS related) was having trouble reading this file so I loaded the records into excel to check a few things out. One of the things I wanted to check was the number of records in the header record and the number in the actual data.

    I tried convert text to columns but the results were a bit confusing as the data had a few blank fields at the end of the record.

    I reverted to each record being text and tried counting the number of commas (this should tell me how many fields). I set up a recursive set of find() formula that searched for commas starting from the location of the last comma plus 1. I then counted the number of these formula that returned a valid result and thus obtained the number of commas in the text.

    BUT - is there an easier way (sans VBA) to do this?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Count Commas (Excel XP)

    If your string is in cell A1 this will give you the number of columns:

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

    It takes the length of the string and substracts the len without the commas to give you the count of commas

    Steve

  3. #3
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Commas (Excel XP)

    Its good to learn something new everyday - I didn't know there was a substitute function. Thanks for the reply Steve.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •