How can we show total count of strings in one column and write results to other column
My sample excel as follows
column1 expected result
abx;xyz 2
a;b;x;LL;xyz 5
Excel looks below
Column1 having list of items and i need the count of strings into other column
4
In the new Excel version, you can use:
=COLUMNS(TEXTSPLIT(A2,";"))
In older versions:
=LEN(A2)-LEN(SUBSTITUTE(A2,";",""))+1
First things first: a “;” is text, too, so what you might want to do is to either
-
split your string of the first column and then count it with TEXTSPLIT (which unluckily doesn’t work in my excel version)
-
count the “;” indirectly by counting the length of the original string, deleting all “;” and then subtracting the new length
=LEN(A1)-(LEN(SUBSTITUTE(A1,”;”,””))
source: Formula in Excel to count occurrences of substrings within strings
Terez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
In newer version of Excel, with access to the TEXTSPLIT function, you can use this code:
=COUNTA(TEXTSPLIT(A2,";",,TRUE))
This will split the text from Cell A2, using semicolons as the delimiters, and ignore any empty results (i.e. “abc;d;;e” will result in 3, not 4, since the “;;” results in no text. If you wish for this to result in 4, then change the TRUE to FALSE)
If you are using an older version of Excel, then you will need to do things slightly differently:
=(LEN(SUBSTITUTE(A2,";",""))>0)+LEN(SUBSTITUTE(";"&A2&";",";;",";"))-LEN(SUBSTITUTE(A2,";",""))-2
This will do the same check for blank/empty values. If you don’t want that, then you can simplify it to:
=(LEN(A2)>0)+LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2,";","")))
The first section checks if there is any data in cell A2, returning 1 or 0. The next bit takes the length of the text in cell A2 (in the first example, after replacing “;;” with “;” to eliminate blank-entries, while the final bit does the same after eliminating all semi-colons. This results in a count of how many semi-colons exist in the string.
(The middle section of that first version adds semi-colons to the start and end of the string. This is to handle cases where the string already starts or ends with a semi-colon, which would result in a blank-value at the start or end.)