No hay una función integrada en Excel para extraer números de una cadena en una celda (y viceversa: elimine la parte numérica y extraiga la parte de texto de una cadena alfanumérica).
Sin embargo, esto se puede hacer usando una función de Excel o algún código VBA simple.
Déjame primero decirte de lo que estoy hablando.
Suponga que tiene un conjunto de datos como el siguiente y desea extraer números de cadenas (como a continuación):
El método que elija también depende de la versión de Excel que esté utilizando:
- Para versiones de Excel anteriores a 2016, debe usar una fórmula un poco más larga
- Para Excel 2016, puede usar la función TEXTJOIN recién introducida
- Método VBA disponible para todas las versiones de Excel
Contenido
Extraiga números de cadenas en Excel (fórmulas de Excel 2016)
Esta fórmula solo funciona en Excel 2016 porque usa la función TEXTJOIN recientemente introducida.
Además, esta fórmula puede extraer números al principio, al final o en medio de una cadena de texto.
Tenga en cuenta que la fórmula TEXTJOIN descrita en esta sección junta todos los caracteres numéricos.Por ejemplo, si el texto es "10 boletos cuestan $200", el resultado es 10200.
Suponga que tiene un conjunto de datos como el siguiente y desea extraer números de cadenas en cada celda:
La siguiente fórmula le dará la parte numérica de una cadena en Excel.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
Esta es una fórmula de matriz, por lo que debe usar " Control + Mayús + Intro " en lugar de usar Enter.
Esta fórmula devolverá un espacio en blanco (cadena vacía) si no hay números en la cadena de texto.
¿Cómo funciona esta fórmula?
Permítanme desglosar esta fórmula e intentar explicar cómo funciona:
- ROW(INDIRECT(“1:”&LEN(A2))) – Esta parte de la fórmula dará una serie de números a partir de uno.La función LEN en la fórmula devuelve el número total de caracteres en la cadena.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {1;2;3 ;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
- (MEDIO(A2,FILA(INDIRECTO(“1:”&LEN(A2))),1)*1) – ¡Esta parte de la fórmula devolverá una matriz de #VALORES!Números o errores basados en cadenas.¡Todos los caracteres de texto en la cadena se convierten en #VALOR!Los errores y todos los valores permanecen como están.Esto sucede cuando multiplicamos la función MID por 1.
- IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) – Cuando se utiliza la función IFERROR, ¡se eliminan todos los #VALORES!Error, solo quedarán números.El resultado de esta parte se ve así: {"";"";"";"";"";"";"";"";"";"";"";"";"";" ";"";"";1;0;0}
- =TEXTJOIN("",VERDADERO,IFERROR((MEDIO(A2,ROW(INDIRECTO("1:"&LEN(A2))),1)*1),"")) – La función TEXTJOIN ahora simplemente combina caracteres de cadena Aún así (solo números) e ignora las cadenas vacías.
Consejo profesional:Si desea verificar la salida de parte de la fórmula, seleccione la celda, presione F2 para ingresar al modo de edición, seleccione la parte de la fórmula que desea generar y presione F9.Verás los resultados inmediatamente.Luego recuerda presionar Control + Z o presionar Escape.No presione entrar.
También puede usar la misma lógica para extraer partes de texto de cadenas alfanuméricas.Aquí está la fórmula para obtener la parte de texto de la cadena:
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))
Un pequeño cambio en esta fórmula es que la función IF se usa para verificar la matriz que obtenemos de la función MID en busca de errores.Si es un error, mantenga el valor, de lo contrario reemplácelo con un espacio en blanco.
Luego use TEXTJOIN para combinar todos los caracteres de texto.
注意: Aunque esta fórmula es válida, utiliza una función volátil (la función INDIRECTA).Esto significa que si lo está utilizando con un gran conjunto de datos, puede llevar algún tiempo obtener resultados.Lo mejor es crear una copia de seguridad antes de usar esta fórmula en Excel.
Extraiga números de cadenas en Excel (para Excel 2013/2010/2007)
Si tiene Excel 2013, 2010 o 2007, no puede usar fórmulas TEXTJOIN, por lo que debe usar fórmulas complejas para hacer esto.
Suponga que tiene un conjunto de datos como el siguiente y desea extraer todos los números en cadenas en cada celda.
La siguiente fórmula hará esto:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2)))), 1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")
Esta fórmula devolverá un espacio en blanco (cadena vacía) si no hay números en la cadena de texto.
Aunque esta es una fórmula matricial, ustedNo hay necesidadUse "Control-Shift-Enter" para usarlo.Una entrada simple funciona para esta fórmula.
El crédito por esta fórmula es para el increíbleSr. Excel Foro.
Nuevamente, esta fórmula extraerá todos los números en la cadena sin importar la posición.Por ejemplo, si el texto es "10 boletos cuestan $200", el resultado es 10200.
注意: Aunque esta fórmula es válida, utiliza una función volátil (la función INDIRECTA).Esto significa que si lo está utilizando con un gran conjunto de datos, puede llevar algún tiempo obtener resultados.Lo mejor es crear una copia de seguridad antes de usar esta fórmula en Excel.
Separe texto y números en Excel con VBA
Si necesita separar texto y números con frecuencia (o extraer números del texto), también puede usar el método VBA.
Todo lo que necesita hacer es crear una función personalizada definida por el usuario (UDF) en Excel usando un código VBA simple y luego usar esa fórmula VBA en lugar de una fórmula larga y compleja.
Permítame mostrarle cómo crear dos fórmulas en VBA: una para extraer números y otra para extraer texto de cadenas.
Extraiga números de cadenas en Excel (usando VBA)
En esta parte, le mostraré cómo crear una función personalizada para obtener solo la parte numérica de una cadena.
Aquí está el código VBA que usaremos para crear esta función personalizada:
Función GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = función final de resultado
Estos son los pasos para crear esta función y luego usarla en una hoja de trabajo:
Ahora podrá usar la función GetText en la hoja de trabajo.Ya que hemos hecho todo el trabajo pesado en el código mismo, todo lo que necesita hacer es usar la fórmula =GetNumeric(A2).
Esto le dará inmediatamente la parte numérica de la cadena.
Tenga en cuenta que, dado que el libro de trabajo ahora contiene código VBA, deberá guardarlo con una extensión .xls o .xlsm.
Si debe usar esta fórmula con frecuencia, también puede guardarla en su libro de macros personal.Esto le permitirá usar esta fórmula personalizada en cualquier libro de Excel que use.
Extraiga texto de una cadena en Excel (usando VBA)
En esta parte, le mostraré cómo crear una función personalizada para obtener solo la parte de texto de una cadena.
Aquí está el código VBA que usaremos para crear esta función personalizada:
Función GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1 ) Siguiente i GetText = Función final de resultado
Estos son los pasos para crear esta función y luego usarla en una hoja de trabajo:
Ahora podrá usar la función GetNumeric en la hoja de trabajo.Ya que hemos hecho todo el trabajo pesado en el código mismo, todo lo que necesita hacer es usar la fórmula =GetText(A2).
Esto le dará inmediatamente la parte numérica de la cadena.
Tenga en cuenta que, dado que el libro de trabajo ahora contiene código VBA, deberá guardarlo con una extensión .xls o .xlsm.
Si debe usar esta fórmula con frecuencia, también puede guardarla en su libro de macros personal.Esto le permitirá usar esta fórmula personalizada en cualquier libro de Excel que use.
