Formato personalizado de números en Excel – Nota II

domingo, mayo 21, 2006

En la nota anterior sobre formatos de números personalizados en Excel, expliqué, brevemente, como crear formatos de números en Excel. En esta nota completaré la explicación mostrando algunos de los códigos con los cuales se crean los formatos.

Para crear formatos personalizados usamos el menú Formato de Celdas---Personalizada. En la ventanilla "Tipo" escribimos la combinación de códigos que crean el formato deseado. Estos códigos son los siguientes:

"General": el formato general de Excel

"#": muestra únicamente los dígitos significativos y no muestra los ceros sin valor.

"0" (cero): muestra los ceros sin valor si un número tiene menos dígitos que ceros en el formato

"?": agrega los espacios de los ceros sin valor a cada lado del separador decimal, para alinear los decimales con el ancho fijo del formato. También se puede utilizar ? para las fracciones que tengan un número de dígitos variable.

"%": porcentaje

[color]: determina el color del número (ver ejemplo en la
entrada anterior). El color debe ser el primer elemento de la sección. Ejemplos de colores: [Negro], [Azul], [Aguamarina], [Verde], [Fucsia] [Rojo], [Blanco], [Amarillo]

"texto": para agregar texto inmediatamente después (o antes) del número, ponga el texto deseado entre comillas, como en el
ejemplo de la entrada anterior.

También se pueden generar formatos condicionales que se aplicarán únicamente si coinciden con las condiciones que se hayan especificado. La condición se escribe entre corchetes y consta de un operador de comparación y un valor. Por ejemplo, el siguiente formato muestra los números iguales o inferiores a 100 en color rojo y los números superiores a 100 en color azul.

[Rojo][<=100];[Azul][>100]

En general es más eficiente utilizar el comando Formato Condicional en el menú Formato para formatos condicionales.

En la próxima nota mostraré los códigos que se utilizan para crear formatos de fechas y horas.


Categorías: Varios_


Technorati Tags:

39 comments:

Rafa Azpiroz,  10 junio, 2008 16:11  

Buenos días,

Estoy pensando en realizar un tipo de dato especial para una hoja de EXCEL y no consigo hacerlo. Se trata de una dirección IP, que son del tipo A.B.C.D, donde A, B, C y D son valores numéricos entre 0 y 255. Necesito también que sea posible ordenarlos siguiendo: primero por el valor de A, en igualdad de condiciones por el valor de B y así hasta D. ¿Es posible hacer esto con formato de personalizado de datos? ¿cómo podría hacerlo de otra forma?

Muchas gracias.

Un saludo.

Jorge L. Dunkelman 10 junio, 2008 18:50  

Con formato personalizado puedes cambiar el aspecto de los datos, es decir, como se ven en el monitor, pero no los datos en si mismos. Si tienes que ordenar las distitnas partes de un texto (la dirección IP es para Excel un texto), tienes que usar funciones o macros.

Sil 08 septiembre, 2008 22:44  

Muy útil la información Jorge! A mí también me causa problemas el formateo de los datos tipo IP en Excel ya que cuando le digo que sea texto, pues convierte a valor exponencial cuando el primer octeto tiene 3 dígitos... :(
Además cuando los 3 últimos octetos tienen 3 dígitos el Excel asume que es un número y lo trata como tal. Si pongo formato de texto a esos casos, el Excel le saca los puntos y en algunos casos inclusive le da el formato exponencial como te decía...

Tienes alguna idea de cómo se puede decir al Excel que estoy usando un dato "tipo IP"?

Muchas gracias!
Silvia

Jorge L. Dunkelman 13 septiembre, 2008 09:54  

Hola Silvia

cuando entras una dirección IP en una celda, Excel la interpreta como texto. He experimentado un poco con los casos que describes, pero no veo que excel le saque los ceros a la dirección o lo que convierta a cualquier otra cosa que no sea texto.
Puedes enviarme el archivo con el problema?

Pedro 06 mayo, 2009 23:33  

por ejemplo como ordenar las filas :
192.168.1.12
192.168.1.2
192.168.1.21
192.168.1.3
192.168.1.31
192.168.1.4
?

Jorge L. Dunkelman 17 mayo, 2009 21:04  

Pedro,
Excel ordena esa lista como texto. ¿Cuál sería el problema?

kamehb 13 agosto, 2009 19:00  

lo mismo que pedro....tengo un rango de ip, y todas desordenadas, se que excel las interpreta como texto pero habra alguna manera de que ordenadas de menor a mayor, tomando el ejemplo de pedro:

192.168.1.12
192.168.1.2
192.168.1.21
192.168.1.3
192.168.1.31
192.168.1.4

a algo asi:

192.168.1.2
192.168.1.3
192.168.1.4
192.168.1.12
192.168.1.21
192.168.1.31

esop..!

Jorge L. Dunkelman 13 agosto, 2009 19:09  

¿Cuál es la lógica de ese orden? ¿Por qué 192.168.1.12 va después de 196.168.1.4?

Camilo E Alzate G 21 agosto, 2009 20:02  

Para excel no es lo mismo 192.168.1.4 que 192.168.1.04, si se antepone el cero, la lista se organiza en el orden que estan buscando.

igual aplica para 04 y 004, deben anteponer tantos ceros como necesiten.

Mandra 03 noviembre, 2009 11:34  

Hola Jorge, estamos intentando una compañera y yo saber como utilizar el comando * para los formatos. Le hemos dado muchas vueltas y no sacamos agua clara, ¿podrias darnos una pista por favor? lo poco que sabemos de este formato es que sirve para repetir el siguiente carácter hasta completar el ancho de la celda.
Muchas gracias por anticipado.

Ana y Toni

Jorge L. Dunkelman 03 noviembre, 2009 17:37  

Ana y Toni, pongamos un ejemplo:
si ponemos el formato @*-, al entrar el texto Jorge en la celda, veremos Jorge-----; o si ponemos un número, por ejemplo, 123.45 veremos 123.45------
Si queremos un formato númerico, por ejemplo para usarlo en una plantilla de factura, podemos usar

#,##0.00*-;-#,##0.00*-

Toni 05 noviembre, 2009 18:43  

Muchas gracias Jorge, recibe un cordial saludo y una felicitación por tu blog que usamos con bastante asiduidad y que nos resulta tan útil.

Ana y Toni

Toni 05 noviembre, 2009 18:44  

Muchas gracias Jorge, recibe un cordial saludo y una felicitación por tu blog que usamos con bastante asiduidad y que nos resulta tan útil.

Ana y Toni

. 20 noviembre, 2009 20:41  

Muy buenas.
Me gustaria saber que codigo tengo que hacer para que cuando escribo un numero de documento de identificacion (DNI en españa) me aparezca por ejemplo 12.123.123-B si lo que he introducido en la celda ha sido 12123123B.

Muchas gracias anticipadas.

Jorge L. Dunkelman 21 noviembre, 2009 01:30  

Dado que 12123123B no es un número sino texto (por la presencia de B al final) no podemos aplicar ningún formato numérico. Existen dos soluciones:
1 - Sin macros: usar una columna auxiliar con una fórmula que haga la transformacion. Por ejemplo, si el número está en la celda A4, en la celda B4 podríamos poner una fórmula como esta

=CONCATENAR(IZQUIERDA(A4;2);".";EXTRAE(A4;3;3);".";EXTRAE(A4;6;3);"-";DERECHA(A4;1))

2 - con macros: programar un evento de manera que al introducir el valor en una celda, éste sea transformado automáticamente.

GNUhOrMiGa,  21 noviembre, 2009 14:15  

Buenos días, Jorge:

he usado el formato condicional 0 añadiéndole "Cine " delante quedanndo como: "Cine "0. Mi problema viene cuando introduzco datos en ese rango de celdas. Mi explico: si introduzco un número cualquiera a mano, efectivamente me lo muestra como Cine 4 (por ejemplo)pero lo que realmente tengo en esas celdad es una función, concretamente la función EXTRAE, la cual sí funciona bien pero únicamente muestra el valor, sin "Cine " delante.

Me podrías indicar que hago mal?? Necesitas más datos??

Esto no es para hacer ningún tipo de plantilla profesinal ni nada por el estilo; estoy realizando ejercicios para practicar excel de cara a una oposición.

Gracias un saludo

Jorge L. Dunkelman 21 noviembre, 2009 18:59  

El resultado de la función EXTRAE, como el de todas las funciones de texto, es texto, no numérico. Por ese motivo Excel no aplica el formato personalizado. Lo que te sugiero hacer es contactenar el resultado de la fórmula a la palabra Cine. Algo así como

="Cine "&EXTRAE(.....

Suerte en la oposición!

GNuhOrMiGa,  21 noviembre, 2009 19:27  

Gracias Jorge

Después de tu explicación, y hacerme ver el tipo de valor obtenido con la función EXTRAE, lo que he aplicado es la función VALOR, anadidando EXTRAE dentro de ésta para convertirlo en numérico, quedando así: =VALOR(EXTRAE(A2;2;1))

Muchas gracias

Anónimo,  23 marzo, 2010 02:42  

Quisiera poner en una celda un formato de hora donde te muestre los : de tal manera que le de 1224 y quede como 12:24 ¿es posible?

Jorge L. Dunkelman 23 marzo, 2010 06:42  

Si, es posible, pero primero una aclaración. Si entras 1224 en una celda Excel no lo interpretará como hora sino con un número corriente. Para que aparezca como 12:24 podemos aplicar el formato personalizado ##":"##, pero esto no convertira la entrada en hora. Excel seguirá "viendo" 1224.
Para que Excel "vea" la hora 12:24, tienes que escribir 12:24, con los dos puntos. Luego puedes usar el formato de hora hh:mm
Te sugiero que leas mis entradas sobre como Excel trata las horas y las fechas.

victor 16 abril, 2010 13:14  

Tengo un problema, intento meter en una celda el formato 01/01 o 02/02 y directamente me lo trata como fecha, pero no quiero que me ponga 1-ene o 2-feb sino como tal y por mucho que cambio a formato de celda genérico, luego me lo cambia a vete tu a saber por qué ¬¬

Por más que copie el formato de otra celda, texto o número especial me sigue tomando como fecha. Odio los automatismos de esta clase.

Ni cambiando el símbolo por un punto, ni como un guión, ni nada, es desesperante que tengas que aceptarlo por tripas.

¿Hay algún modo de "engañar" al programa?

Jorge L. Dunkelman 16 abril, 2010 18:31  

Si estás intentando ingresar fracciones tienes que darle el formato de fracción a la celda (o a todo el rango) antes de ingresar el número.
Si se trata de texto, darle formato de texto, siempre antes de ingresar el valor, o anteceder el texto con una comilla (').

Anónimo,  06 enero, 2011 22:56  

Jorge estoy utilizando el formato #0":"#0":"#0";"#0 para hh:mm:ss;ff
ff serían los frames, los cuales deberian ir solo hasta 30, pero el excel me lo toma como rango de 0 a 100. O sea que cuando sumo 100 frames recien ahi me suma un segundo. En realidad quiero que al sumar 30 frames, sume un segundo más.
Ejemplo:

quiero lograr:

01:22:30:24
02:05:10:20 +
-----------
03:27:41:14

pero lo que logro es
01:22:30:24
02:05:10:20 +
-----------
03:27:40:44

Jorge L. Dunkelman 07 enero, 2011 17:14  

Te sugiero ver lo que escribo en esta nota.

Mario 10 junio, 2011 22:02  

Jorge, te comento esperando tu ayuda!

En la celda A1 tengo un codigo
En la ceda B1 tengo una formula para que valla a buscar el codigo de A1 y lo despliege en la celda B1 (el valor que devuelve es un numero de 0 a 100)

Quiero que si el valor que devuelva sea 0 lo pinte de rojo y en caso contrario lo pinte de negro

para ello tengo este formato condicional [Rojo][=0]Estándar;[Negro][>0]Estándar

Pero tambien quiero que ese numero lo devuelva en formato de %

Ejemplo

0 (en color rojo)% y 100 (en color negro) %

de antemano muchas gracias!

Jorge L. Dunkelman 11 junio, 2011 06:45  

Tendría que ser

[Rojo][=0]0%;0%

No hace falta señalar [negro] ya que este es el color por defecto.
También podrías usar formato condicional para establecer el color de la fuente.

Mario 11 junio, 2011 16:46  

Gracias por tu ayuda Jorge!

Anónimo,  19 octubre, 2011 05:03  

Jorge, Felicidades por tu Blog.

Me gustaría que me ayudaras con esta consulta.

Necesito hacer referencia a varias celdas de una hoja desde otra. El tema es que algunas tienen el formato personalizado "<"0,000; es decir que si tengo un valor como 0,064 este debe aparecer como <0,064. He intentado haciendo =(Datos!C10)
pero solo me aparece el valor sin el formato.

¿Existe alguna forma de resolverlo sin usar el botón para copiar formato??

Jorge L. Dunkelman 19 octubre, 2011 12:35  

Te acabo de responder en el comentario que dejaste en el otro post sobre el tema

Anónimo,  16 marzo, 2012 19:40  

Jorge, quiero realizar operaciones con numeros IPs.
Ejemplo:
quiero restar 1 a un numero ip.
10.123.26.23 a 10.123.26.22
10.123.20.200 a 10.123.20.199

agradeciendo tus respuesta saludos.

Jorge L. Dunkelman 19 marzo, 2012 19:35  

Los IP no son números, sino texto. Por eso no los puedes usar en operaciones matemáticas. Suponiendo que el IP se encuentra en la celda B3, puedes usar esta fórmula

=EXTRAE(B3;1;10)&VALOR(DERECHA(B3;LARGO(B3)-10))-1

Anónimo,  15 febrero, 2013 16:59  

Buenas tardes, necesito dar formato a lo siguiente: Debo ingresar datos que siempre comienzan con IC04- luego viene un número de 6 dígitos y finalmente terminan con - y alguna letra (Ej, IC04-090047-V, IC04-128969-W, etc) para realizar el trabajo de manera más rápida, utilicé los formatos, para introducir solo los números, el pre-fijo me lo respeta bárbaro, pero no logro que ingrese la última letra. Ej. Yo quiero ingresar 090047V y que por el formato me aparezca IC04-090047-V o 128969W y que aparezca IC04-128969-W. Alquien podrá ayudarme? Desde ya muchas gracias!

Jorge L. Dunkelman 17 febrero, 2013 19:22  

Hola, al poner en la celda 090047V Excel no ve un número sino un texto. Por eso no puede aplicar el formato. Si el dígito final fuera siempre el mismo, podrías usar simplemente el formato personalizado "IC04-"#"-V".
Podrías usar una columna auxiliar con esta fórmula
="IC04-"&IZQUIERDA(A1,6)&"-"&DERECHA(A1,1)

donde en A1 ponés 090047V

Anónimo,  22 febrero, 2013 18:19  

Hola! Tengo una columna de datos donde puedo tener diferentes variantes, tales como:
2283-3214
2122-33
0138-17
Tengo que llegar a tener en total 10 caracteres en todos los casos y si es necesario, completarlos con cero a la izquierda, quedando así:
02283-3214
0002122-33
0000138-17
Hay algún modo? Gracias!

Jorge L. Dunkelman 23 febrero, 2013 20:52  

No me parece que con formato personalizado; pero podrías usar esta fórmula

=REPETIR(0;10-LARGO(A1))&A1

donde A1 es la celda que contiene el valor original.

Alexander Cristaldo 13 marzo, 2013 06:01  

necesito saber como seria el formato personalizado para la cedula de identidad uruguaya del tipo 1.234.567-8 he intente varios formatos y quedan de la siguiente manera: 12.345.67-5, agradezco la ayuda

Jorge L. Dunkelman 13 marzo, 2013 06:55  

Hola Alexander,
podés usar este formato personalizado

0"."000"."000"-"0

Anónimo,  20 febrero, 2014 20:09  

hola como puedo hacer para que cuando introduzca numeros de tarjeta que estan compuestos de 4 bloques de 4 no me aproxime el ultimo numero a 0 ??

Jorge Dunkelman 21 febrero, 2014 11:07  

La forma más práctica es dar a la celda formato Texto, antes de ingresar el número, o anteponer un apóstrofe (') al número al ingesarlo manualmente. Fijate en esta nota.

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP