practicaexcel

SUSTITUIR en Excel: cómo cambiar un texto por otro

SUSTITUIR · en inglés SUBSTITUTE · Texto

Actualizado el

SUSTITUIR busca un texto dentro de una celda y lo cambia por otro. Sirve para quitar los espacios de un teléfono, cambiar un guion por una barra o corregir una palabra mal escrita en toda una columna, sin tocar los datos originales.

En inglés se llama SUBSTITUTE y está en todas las versiones de Excel. Tiene una hermana, REEMPLAZAR, que no busca un texto sino una posición: cambia los caracteres que hay en un sitio concreto, sean cuales sean. En el ejemplo 3 verás cuándo te conviene cada una.

Separador:

Sintaxis

=SUSTITUIR(texto; texto_original; texto_nuevo; [núm_de_instancia])
texto
El texto donde buscas, normalmente una celda.
texto_original
Lo que quieres cambiar, entre comillas: " ", "-", "Sr.". Tiene que estar escrito igual, también las mayúsculas.
texto_nuevo
Lo que pones en su lugar. Para borrar, escribe dos comillas seguidas: "".
núm_de_instancia
Opcional. Qué aparición cambiar: 1 la primera, 2 la segunda… Si no lo pones, cambia todas.

REEMPLAZAR va por posiciones: =REEMPLAZAR(texto_original; núm_inicial; núm_de_caracteres; texto_nuevo). Le dices en qué carácter empieza y cuántos quita, y pone el texto nuevo en ese hueco. Las dos devuelven siempre texto, aunque el resultado sean cifras.

Ejemplos

Ejemplo 1: quitar espacios y guiones de un teléfono

Has juntado los teléfonos de tus clientes y cada uno viene escrito a su manera: con espacios, con guiones o con las dos cosas. El programa de envío de SMS los quiere solo con cifras.

D2fx=SUSTITUIR(SUSTITUIR(B2;" ";"");"-";"")
ABCD
1ClienteTeléfonoSin espaciosSolo cifras
2Inés Calvo600 123 456600123456600123456
3Raúl Méndez611-45-78-90611-45-78-90611457890
4Carla Ortega622 33-44-5562233-44-55622334455
5Diego Prieto91 555 12 34915551234915551234

Con "" como texto nuevo, SUSTITUIR borra lo que encuentra. En la columna C solo se van los espacios, y el teléfono de Raúl se queda igual. Para quitar dos cosas, mete un SUSTITUIR dentro de otro: el de dentro quita los espacios y el de fuera, los guiones.

Ejemplo 2: cambiar solo una de las apariciones

Una agencia de viajes guarda las rutas de ida y vuelta como «MAD-CUN-MAD». Algunos clientes vuelven a otro aeropuerto, y hay que cambiar solo el código del regreso, que es la segunda vez que aparece MAD.

D2fx=SUSTITUIR(A2;"MAD";B2;2)
ABCD
1RutaVuelve aSin el 4.º argumentoCon el 4.º argumento
2MAD-CUN-MADBCNBCN-CUN-BCNMAD-CUN-BCN
3MAD-LIM-MADVLCVLC-LIM-VLCMAD-LIM-VLC
4MAD-MEX-MADAGPAGP-MEX-AGPMAD-MEX-AGP
5MAD-BOG-MADMADMAD-BOG-MADMAD-BOG-MAD

Sin el último argumento, SUSTITUIR cambia todas las apariciones, y el viaje saldría de Barcelona. Con un 2 solo toca la segunda. El texto nuevo puede salir de una celda, como aquí la columna B. Si pides una aparición que no existe, por ejemplo la 3, devuelve el texto tal cual, sin error.

Ejemplo 3: REEMPLAZAR: tapar parte de un teléfono

Vas a publicar los ganadores de un sorteo y no quieres mostrar sus teléfonos completos. Aquí no sabes qué cifras hay, solo dónde están: las 6 primeras. Eso es trabajo de REEMPLAZAR.

C2fx=REEMPLAZAR(B2;1;6;"******")
ABC
1GanadorTeléfonoPublicado
2Paula Lorenzo612345678******678
3Iker Navas698765432******432
4Noelia Soto655019283******283
5Ramiro Aguilar633470915******915

REEMPLAZAR empieza en el carácter 1, quita 6 y pone los seis asteriscos en su lugar. Con SUSTITUIR no podrías, porque tendrías que decirle qué cifras buscar, y en cada fila son otras. La regla: si sabes qué quieres cambiar, SUSTITUIR; si sabes dónde está, REEMPLAZAR.

Ejemplo 4: contar cuántas veces aparece algo

En un recetario, la columna B lista los ingredientes separados por comas. Quieres saber cuántos lleva cada receta sin contarlos a mano. El truco: quitas las comas con SUSTITUIR y miras con LARGO cuántos caracteres han desaparecido.

C2fx=LARGO(B2)-LARGO(SUSTITUIR(B2;",";""))+1
ABC
1RecetaIngredientesCuántos
2Gazpachotomate, pepino, pimiento, ajo, aceite, vinagre6
3Guacamoleaguacate, cebolla, cilantro, limón, sal5
4Cevichepescado, limón, cebolla morada, ají, cilantro, sal6
5Arepasharina de maíz, agua, sal3

Si una celda tiene 5 comas, sin ellas mide 5 caracteres menos. Hay un ingrediente más que comas, por eso se suma 1. Cambia la coma por otro texto para contar otras cosas: con " " cuentas los espacios, y sumando 1, las palabras.

Pruébalo aquí

Los códigos de producto de la columna A usan barras, como «CAM/ROJ/M». En B2, escribe una fórmula que cambie todas las barras de A2 por guiones.

B2fx
AB
1CódigoCon guiones
2CAM/ROJ/M
3PAN/AZU/42
4SUD/GRI/L
5ZAP/NEG/40
Pulsa el botón (o toca la hoja) y escribe la fórmula en B2: se corrige al momento.

Errores frecuentes

No cambia nada
El texto_original no está escrito igual que en la celda. SUSTITUIR distingue mayúsculas: "mad" no encuentra MAD. También pasa con el espacio «duro» de los textos copiados de una web, que parece un espacio normal pero no lo es. Copia el texto tal como aparece en la celda. Para el espacio duro, búscalo por su código: =SUSTITUIR(A2;CARACTER(160);" ").
#¡VALOR!
El núm_de_instancia es 0 o negativo. En REEMPLAZAR, la posición inicial es 0 o el número de caracteres es negativo. Las apariciones y las posiciones se cuentan desde 1.
La suma da 0
SUSTITUIR y REEMPLAZAR devuelven texto, aunque el resultado sean cifras, y SUMA se salta los textos. Multiplica el resultado por 1 o usa VALOR: =SUSTITUIR(B2;" ";"")*1.
#¿NOMBRE?
Has escrito SUBSTITUTE, el nombre en inglés, o falta cerrar unas comillas. En Excel en español es SUSTITUIR, y cada texto va entre un par de comillas.

Trucos

Preguntas frecuentes

¿Qué diferencia hay entre SUSTITUIR y REEMPLAZAR?

SUSTITUIR busca un texto y lo cambia allí donde esté: le dices qué cambiar. REEMPLAZAR cambia los caracteres de una posición, sean cuales sean: le dices dónde empezar y cuántos quitar. Para quitar guiones, SUSTITUIR; para tapar las primeras cifras de un teléfono, REEMPLAZAR.

¿Cómo quito todos los espacios de una celda?

Con =SUSTITUIR(A2;" ";""), que quita todos, también los de en medio. Si solo quieres quitar los que sobran (al principio, al final y los dobles), usa ESPACIOS.

¿Cómo quito las tildes de un texto?

Anidando un SUSTITUIR por cada vocal: =SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(A2;"á";"a");"é";"e");"í";"i");"ó";"o");"ú";"u"). Si el texto tiene mayúsculas con tilde, añade también Á, É, Í, Ó y Ú.

¿Cómo cambio los puntos por comas?

Con =SUSTITUIR(A2;".";","). Si son números que llegan de otro programa con punto decimal, es más práctico seleccionar la columna y usar Buscar y reemplazar (Ctrl+L): cambias las celdas originales y Excel las reconoce como números.

¿Se escribe con punto y coma o con coma?

Depende de tu país: con punto y coma en España, Argentina, Colombia o Chile, y con coma en México o Perú. =SUSTITUIR(A2;"-";"/") en España es =SUSTITUIR(A2,"-","/") en México. Con el selector de arriba ves todos los ejemplos de las dos formas.

Funciones relacionadas