01 / 11Los Avisos Decían "Desde 46419"

Funciones de TextoExcelFormatos de Fecha

Función TEXTO en Excel: unir texto con fechas y números

Nueve Avisos de Subida de Alquiler Decían que la Nueva Renta Empezaba el 46419, Porque & Une el Número que Hay Bajo una Fecha, No la Fecha que Se Ve

31 oct 20269 min de lectura

UsaCONCAT y &MONEDAREDONDEARCONTAR, CONTARA

Hollins Lettings subió catorce alquileres un 3,5% a partir del 1 de febrero. Una sola fórmula escribió todos los avisos. Nueve daban como fecha de inicio 46419, hubo que enviarlos de nuevo y las subidas empezaron dos meses tarde.

Revisión de rentas, 1 de febrero de 2027Cifra
Avisos enviados14
Fechas impresas como 464199
Rentas citadas con tres decimales7
Subida mensual en los nueve pisos373,30 £
Meses perdidos2
Renta perdida746,60 £

Alcance. TEXTO, REDONDEAR, MONEDA y el operador & funcionan en todas las versiones de Excel. UNIRCADENAS, en la sección 7, requiere Excel 2019 o Microsoft 365.

En todo el artículo, la hoja de revisión está en A1:G15: piso en A, renta actual en B, renta nueva en C y fecha de inicio en D. El aviso terminado va en la columna H.

01Los Avisos Decían "Desde 46419"

Los contratos de alquiler exigen dos meses de aviso por escrito con la nueva renta y la fecha en que empieza. La asistente construyó la frase una vez y la rellenó hacia abajo en catorce filas.

="Nueva renta de "&A2&": "&C2&" £ al mes desde "&D2

En Alder Ct 4 produjo Nueva renta de Alder Ct 4: 1319.625 £ al mes desde 46419. Los avisos salieron el 25 de noviembre, y nadie leyó ninguno antes de enviarlo.

Catorce Avisos, Nueve Fechas Impresas como 46419

La hoja de revisión de rentas de Hollins Lettings, en A1:G15. La columna C es la renta actual por 1,035, con formato de dos decimales, y la D la fecha de inicio, con formato dd/mm/aaaa. La columna E indica si D guarda una fecha real o texto. Las columnas F y G son lo que el & de la fórmula del aviso escribió en cada frase: la renta con todos sus decimales y la fecha como número de serie allí donde D tenía una fecha real.

ABCDEFG
1
Flat
Current rent
New rent
Starts
Date stored as
Rent in notice
Date in notice
2
Alder Ct 1
850
879.75
01/02/2027
Text
£879.75
01/02/2027
3
Alder Ct 2
980
1014.3
01/02/2027
Date
£1014.3
46419
4
Alder Ct 4
1275
1319.625
01/02/2027
Date
£1319.625
46419
5
Brook Hse 3
1150
1190.25
01/02/2027
Text
£1190.25
01/02/2027
6
Brook Hse 7
1090
1128.15
01/02/2027
Date
£1128.15
46419
7
Cedar Mews 2
1425
1474.875
01/02/2027
Date
£1474.875
46419
8
Cedar Mews 5
925
957.375
01/02/2027
Text
£957.375
01/02/2027
9
Dene Rd 14
1210
1252.35
01/02/2027
Date
£1252.35
46419
10
Dene Rd 16
1365
1412.775
01/02/2027
Date
£1412.775
46419
11
Elm Yard 1
795
822.825
01/02/2027
Text
£822.825
01/02/2027
12
Elm Yard 6
1040
1076.4
01/02/2027
Date
£1076.4
46419
13
Fenn St 9
1185
1226.475
01/02/2027
Date
£1226.475
46419
14
Fenn St 11
1330
1376.55
01/02/2027
Text
£1376.55
01/02/2027
15
Garth Ln 2
1095
1133.325
01/02/2027
Date
£1133.325
46419

fxLas celdas con fórmulas están resaltadas en verde

Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas

Tres inquilinos llamaron para preguntar qué significaba 46419. Cuando la oficina reabrió el 6 de enero, un nuevo aviso de dos meses solo podía surtir efecto desde el 1 de abril.

02& Une el Valor, No el Formato

Una celda guarda un valor y lo muestra a través de un formato. El operador & toma el valor y descarta el formato. Una fecha es, por debajo, un número de serie, así que se une como número de serie.

La celda muestraValor de fondoUnido con &
01/02/20274641946419
1.319,63 £1319,6251319,625
1.014,30 £1014,31014,3
3,5%0,0350,035

Lo mismo ocurre con CONCAT, CONCATENAR y UNIRCADENAS. Ninguna ve el formato, así que ninguna puede copiarlo. El símbolo de libra, el separador de miles, el cero final y las barras viven en el formato.

Escenario: Pon una fecha en D2 con formato de fecha. En otra celda escribe ="desde "&D2 y lee el número de serie que devuelve. Después da a D2 formato de porcentaje y observa que el texto unido no cambia en absoluto.

Pruébalo en la cuadrícula

03Las Fechas Erróneas Eran las Auténticas

Cinco avisos se leían bien, y esa fue la pista. Sus fechas eran texto, importadas de la exportación de la agencia anterior, así que & no tenía nada que convertir. Las otras nueve se habían escrito a mano como fechas reales.

Las dos clases muestran 01/02/2027 en la hoja, y solo una es una fecha. Cuéntalas:

=CONTAR(D2:D15)

Devuelve 9, porque CONTAR cuenta números y una fecha real es un número. Catorce filas y nueve números significan que cinco fechas son texto.

Las cinco fechas de texto son las que hay que arreglar, aunque sus avisos salieran bien. El texto no se puede ordenar por fecha ni sumar, y la próxima fórmula que calcule con él fallará.

Conviértelas con =FECHANUMERO(D2) y pega los valores encima.

Escenario: Escribe =CONTAR(D2:D15) junto a la hoja y comprueba que devuelve 9. Después escribe =ESTEXTO(D2) en una columna libre, rellénala hacia abajo y localiza los cinco pisos heredados de la agencia anterior.

Pruébalo en la cuadrícula

04TEXTO Escribe el Valor con un Formato

TEXTO toma un valor y un código de formato, y devuelve el texto que la celda habría mostrado. Es la única herramienta que lleva un formato dentro de una frase.

=TEXTO(D2;"d mmmm aaaa")      → 1 febrero 2027
=TEXTO(C2;"#.##0,00 £")       → 1.319,63 £
=TEXTO(0,035;"0,0%")          → 3,5%

Los códigos son los de Formato de celdas, Personalizada. d es el día sin cero inicial, mmmm el mes completo y aaaa el año. En un código numérico, 0 obliga a mostrar un dígito y #.##0 añade el separador de miles.

El aviso pasa a ser una frase que se lee igual para cada piso:

="Nueva renta de "&A2&": "&TEXTO(C2;"#.##0,00 £")
 &" al mes desde el "&TEXTO(D2;"d mmmm aaaa")

Una fecha escrita como 1 febrero 2027 no la confunde nadie con el 2 de enero, aunque esté acostumbrado a las fechas americanas. Para lo que sale de la oficina, el mes en letra es el código más seguro.

Escenario: Escribe =TEXTO(D2;"d mmmm aaaa") junto a la fecha de inicio y comprueba que devuelve 1 febrero 2027. Después cambia el código a "dddd d mmmm aaaa" y comprueba que el día de la semana sale en español.

Pruébalo en la cuadrícula

05TEXTO Redondea las Palabras, No la Renta

TEXTO(C2;"#.##0,00 £") convierte 1319,625 en 1.319,63 £ dentro del aviso. La renta de la columna C sigue siendo 1319,625, y eso es lo que el libro de rentas cobra cada mes.

Siete de las catorce rentas nuevas tenían un tercer decimal. Sus avisos prometían una cifra y el libro esperaba otra, con medio penique de diferencia.

PisoRenta nueva en CAviso con TEXTO
Alder Ct 41319,6251.319,63 £
Cedar Mews 21474,8751.474,88 £
Fenn St 91226,4751.226,48 £

El arreglo va en la columna C, no en la frase. Redondea la renta donde se calcula, y todas las cifras que dependen de ella coincidirán con el aviso:

=REDONDEAR(B2*1,035;2)

Escenario: Sustituye la fórmula de la renta nueva en C2 por =REDONDEAR(B2*1,035;2) y rellénala hacia abajo. Comprueba que C3 guarda ahora exactamente 1319,63, y que el aviso y la celda coinciden en cada fila.

Pruébalo en la cuadrícula

06Los Códigos Dependen del Idioma de Excel

El código de formato dentro de TEXTO es un texto. Excel no lo traduce cuando el libro se abre en otra versión de idioma, como sí hace con los nombres de las funciones.

El Excel en español escribe el año como aaaa y usa coma decimal, así que sus códigos son "d mmmm aaaa" y "#.##0,00". El Excel en inglés usa yyyy y #,##0.00.

Un código escrito para un idioma puede salir mal en el otro, sin ningún error.

Si un libro viaja entre países, mantén la fecha de la frase corta e inequívoca. Escribir el mes en letra evita confundir día y mes; el nombre del mes seguirá el idioma del Excel que calcula.

07MONEDA y UNIRCADENAS

MONEDA es un atajo para importes. =MONEDA(C2;2) devuelve el importe con símbolo de moneda, separador de miles y dos decimales. El símbolo sale de la configuración regional del equipo, no del libro.

En los equipos británicos de Hollins escribe £1,319.63. Abierto por un colaborador en Madrid escribe 1.319,63 €. Usa TEXTO con el símbolo dentro del código cuando la moneda importa.

UNIRCADENAS une una lista y pierde el formato igual que &. Para enumerar las fechas de inicio de varios pisos, envuelve primero el rango en TEXTO:

=UNIRCADENAS(", ";VERDADERO;TEXTO(D2:D4;"d mmm aaaa"))

Escenario: Escribe =MONEDA(C2;2) en una celda y fíjate en qué símbolo de moneda elige. Después decide si ese símbolo es el que debe llevar una carta a un inquilino británico, y escribe la alternativa con TEXTO.

Pruébalo en la cuadrícula

08Comprueba la Frase Antes de Enviarla

Catorce avisos son pocos para leerlos, y nadie los leyó. Una fórmula puede leerlos por ti. Un aviso que nombra 2027 imprimió una fecha; uno que no lo nombra imprimió un número de serie.

=ESNUMERO(HALLAR("2027";H2))

En los avisos originales devuelve FALSO nueve veces. Para las rentas, la regla sencilla funciona mejor que cualquier comprobación: escribe con TEXTO cada número de una frase, y no quedará ninguno con tres decimales.

Escenario: Construye el aviso original en H2:H15 y escribe =ESNUMERO(HALLAR("2027";H2)) en I2, rellenándolo hacia abajo. Cuenta los FALSO, reconstruye H con TEXTO y confirma que todas las filas pasan a VERDADERO.

Pruébalo en la cuadrícula

09Seis Trampas

  1. Unir una fecha con &. Llega como un número de serie, por ejemplo 46419.
  2. Unir un importe con &. El símbolo, los separadores y el cero final desaparecen, y pasan todos los decimales.
  3. Fiarse de las filas que parecen bien. Las fechas guardadas como texto se unen bien y rompen todo lo demás.
  4. Redondear solo las palabras. TEXTO redondea el aviso; la celda sigue guardando la cifra sin redondear.
  5. Códigos en otro idioma. aaaa y yyyy no son intercambiables, y Excel no los traduce.
  6. MONEDA en el equipo de otra persona. El símbolo sigue su configuración regional, no la tuya.

10Cuatro Ejercicios

  1. Reprodúcelo. Construye el aviso con & y confirma el número de serie en la fecha.
  2. Encuentra las fechas de texto. Usa CONTAR y ESTEXTO para localizar las cinco filas que no son fechas reales.
  3. Escríbelo bien. Reconstruye el aviso con TEXTO para la renta y para la fecha.
  4. Demuéstralo. Redondea la renta con REDONDEAR y comprueba que cada aviso nombra 2027 y coincide con la columna C.

Qué Llevarse

La hoja de Hollins era correcta. Cada renta y cada fecha estaban bien, y cada celda mostraba exactamente lo que debía.

Un formato es el aspecto de una celda, y & nunca lo ve. Cuando un número o una fecha entra en una frase, escríbelo con TEXTO, redondea la cifra donde se calcula y lee una frase terminada antes de que salga de la oficina.

Comparte este artículo:
Volver al Blog

Practícalo

CONCAT y &

Un ejercicio en una cuadrícula real

Abrir el ejercicio

Las funciones que usa este artículo

Sintaxis, ejemplos resueltos y los errores que esperar: una página de referencia para cada una.

Sigue leyendo

  1. Texto en columnas en Excel: dividir una columna en variasLeer el artículo
  2. Guardar Excel como CSV sin perder datos: qué se pierdeLeer el artículo
  3. Cómo detectar valores fijos en fórmulas de ExcelLeer el artículo
Reto diario · Día 107

Suma los kilómetros de un periodo de liquidación con SUMAR.SI.CONJUNTO

Un ejercicio nuevo cada día, resuelto en una cuadrícula real.

Resolver el reto de hoy