Jeff tiene una hoja de trabajo con bastante texto, en la columna C. Le gustaría contar las ocurrencias de una cadena específica dentro de la columna C, pero solo para filas impares (1, 3, 5, etc.) en los datos. Es posible que la cadena que está buscando no sea el contenido completo de la celda y puede ocurrir varias veces dentro de una celda en particular. (Si ocurre 2 o 3 veces en una celda, debería contar como 2 o 3 ocurrencias). Se pregunta si existe una forma de fórmula para hacer esto, sin recurrir a una macro.

Una forma de abordar esto es mediante el uso de una columna auxiliar. Por ejemplo, digamos que puede usar la columna D como columna auxiliar, y la primera celda que contiene datos es la celda C2. (Quizás la celda C1 tiene un encabezado de columna). Puede usar la siguiente fórmula en la celda D2:

=IF(MOD(ROW(),2)=1,(LEN(C2)-LEN(SUBSTITUTE(C2,"my text","")))/LEN("my text"),"")

Todo lo que necesita hacer es reemplazar la cadena de búsqueda («mi texto») con lo que esté buscando. La función LEN se usa dos veces, primero para encontrar la longitud de lo que esté en la celda C2 y luego para restarle la longitud del texto con todas las instancias de «mi texto» eliminadas. Luego, este valor se divide por la longitud de lo que está buscando, lo que da como resultado cuántas instancias del texto de búsqueda había en la celda. Tenga en cuenta que la función SI garantiza que se devuelva un valor numérico solo si la fila es una fila impar.

Puede copiar esta fórmula tantas filas como sea necesario y luego sumar la columna. El resultado es el número de veces que la cadena aparece en filas impares.

Si el diseño de su hoja de trabajo no le permite utilizar una columna auxiliar, puede confiar en una fórmula que funcione en matrices de datos.

Aquí hay uno que hace el truco:

=SUMPRODUCT((LEN(C:C)-LEN(SUBSTITUTE(C:C,"my text","")))/LEN("my text")*ISODD(ROW(C:C)))

Esta fórmula esencialmente hace lo mismo que la fórmula anterior, excepto que la función SUMPRODUCTO realiza el cálculo internamente para cada celda en la columna C.Debe darse cuenta de que, dado que la fórmula examina toda la columna C, eso significa que si su texto de búsqueda («mi texto «) aparece dentro de cualquier celda que no sea de datos en la columna (como el encabezado de una columna), luego también se incluirá en el total devuelto.

Si decide utilizar una macro, puede crear fácilmente una función definida por el usuario que examine un rango de celdas y determine el recuento. El siguiente es un ejemplo del tipo de macro que podría usar:

Function CountInst(rSource As Range, sSearch As String, bCaseInsensitive As Boolean) As Integer     Dim c As Range     Dim iCount As Integer     Dim sTemp1 As String     Dim sTemp2 As String

sTemp2 = sSearch     If bCaseInsensitive Then sTemp2 = LCase(sTemp2)

iCount = 0

For Each c In rSource         If c.Row Mod 2 = 1 Then             sTemp1 = c.Text             If bCaseInsensitive Then sTemp1 = LCase(sTemp1)



iCount = iCount + (Len(sTemp1) - _               Len(Replace(sTemp1, sTemp2, ""))) / Len(sTemp2)

End If     Next c

CountInst = iCount End Function

Para usar esto, todo lo que necesita hacer es proporcionar un rango, lo que desea buscar y si desea que la coincidencia no distinga entre mayúsculas y minúsculas o no. Por ejemplo, si quieres buscar «mi texto» y no importa el caso, usarías lo siguiente:

=CountInst(C2:C99,"my text",TRUE)

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (1514) se aplica a Microsoft Excel 2007, 2010, 2013 y 2016.