texto - substring excel español

Fórmula en Excel para contar las ocurrencias de subcadenas dentro de cadenas

Question

Estoy tratando de contar el número de veces que aparece una subcadena dentro de una columna de datos de cadena en Excel. Por favor vea el siguiente ejemplo.

La columna de datos de cadena (tweets) se ve así:

   A
1  An example string with @username in it
2  RT @AwesomeUser says @username is awesome

La columna con "subcadenas" (nombres de pantalla de Twitter) se ve así:

   B
1  username
2  AwesomeUser

Quiero usar una fórmula para contar el número de veces que aparece una subcadena de B1, B2, etc. en las cadenas de la columna A. Por ejemplo: una fórmula que busque B1 devolverá "2" y una búsqueda de B2 devolvería "1".

No puedo hacerlo de esta manera:

=COUNTIF(A:A, "username")

Porque COUNTIF solo busca cadenas, no subcadenas. Esta fórmula siempre devolvería "0".

Aquí hay una formula que pensé que podría hacerlo:

=SUMPRODUCT((LEN(A:A)-(LEN(SUBSTITUTE(A:A,"username",""))))/LEN("username"))

Desafortunadamente, tengo 16,000 entradas en la columna B y decenas de miles en A, por lo que contar caracteres no funcionará incluso en una PC de alta potencia (también, el resultado devuelto por la función es sospechoso).

Pensé en usar:

=COUNTIF(A:A, "*username*")

pero COUNTIF requiere una cadena con los operadores estrella; Necesito usar referencias de celda debido al volumen de datos.

Mi pregunta: ¿Alguien sabe cómo puedo usar una fórmula para esto? Si uso COUNTIF, ¿cómo obtengo una referencia de celda en la parte condicional de la declaración (o uso una función para sustituir la cadena en la celda referenciada dentro de la parte condicional de una declaración COUNTIF)?

Sé que podría analizar los datos, pero me gustaría saber cómo hacerlo en Excel.

Answer 1

Ya casi estás ahí, usa

=COUNTIF(A:A, "*"&B1&"*")

(probado en Excel 2010)

Answer 2

Una solución de Excel muy simple para la aparición de x-string ("ABC") en y-string ("ABC 123 ABC 23dfABC"):

1. Identificar longitud de y-string. Ly = len (y)
2. Sustituya la cadena x por la cadena x1 (agregue solo 1 carácter de cualquier carácter compatible a la cadena x. Por ejemplo, "ABC" => "ABC_") y calcule la longitud de la nueva cadena y1-string. Ly1 = len (sustituto (y, x, x1)).
3. No de ocurrencia de x-string) en y-string = Ly1-Ly

Número de ocurrencias: len (sustituto (y, x, x1)) - len (y)

Nomenclatura para el entendimiento: x-string: "ABC" x1-string: "ABC_" y-string: "ABC 123 ABC 23dfABC" y1-string: "" ABC_ 123 ABC_ 23dfABC_ "