13 de abril de 2010

¿Cómo definir formatos personalizados en Excel?

Algunas preguntas frecuentes:

1. ¿Qué es un formato?
El formato se refiere al cambio de contenido y aspecto de una o varias celdas seleccionadas.

2. ¿Qué formato viene por defecto en Excel?

Excel puede hacer por defecto lo siguiente:
  • Si es un texto, Excel lo alinea a la izquierda.
  • Si es un número, Excel sabe que es un número y aunque no lo coloca con formato numérico, lo hace con formato general para poder hacer cálculos con dichas celdas.
  • Si es una fecha, Excel le da formato de celda, pero se debe de tener en cuenta que la fecha por lo menos deberá estar separada por un barra (/) o un guion (-), de lo contrario será en formato general.

3. ¿Cómo abrir la ventana de Formato de celda?

  • Pestaña Inicio/Formato/Formato de celdas.
  • Clic derecho a la celda que desea colocarle el formato

De cualquiera de las 2 opciones, se abrirá la siguiente ventana



En el campo "Tipo" escriba el código del formato que desea crear.

Antes de crear dicho formato personalizado debe conocer las reglas sobre cómo crear un formato de celda, de la siguiente forma que contiene 4 secciones


  1. Definirá el formato del número de la celda si éste es positivo.
  2. Hará lo mismo que la primera sección pero para los números negativos.
  3. Definirá el formato del número en la celda si el número vale cero.
  4. Definirá el formato del número en la celda si ésta tiene texto.

  • 0 (Cero): muestra un dÍgito en una celda, incluyendo el dígito 0. Por ejemplo, el formato 0,00 muestra el número 0,987 como un número con dos lugares después de la coma decimal, esto es, 0,99 (sin el 7)

  • # (Numeral): Muestra los dígitos significativos y no muestra los ceros no significativos. Un formato con dos lugares luego de la coma decimal, con o sin el dígito cero, por ejemplo, el código de formato para 50 centavos:
          #,##: La celda muestra ,5.
          #,#0: La celda muestra ,50.
          0,00: La celda muestra 0,50.

  • ?: Reserva un dígito, añade espacios en ceros insignificantes.
  • . : Punto decimal.
  • %: Símbolo porcentual.
  • , : Separador de millares.
  • E- E+ e- e+: Notación Científica.
  • $ - + / (): : Muestra estos caracteres, no necesitan comillas.
  • \: Muestra el siguiente caracter en el formato.
  • * : Repite el siguiente caracter hasta completar el ancho de la celda.
  • _: Deja un espacio 
  • "texto": Muestra el texto escrito entre las comillas dobles
  • @: Espacio reservado para un texto.
  • [color] : Muestra el carácter en el color especifico.
  • [color n ] : Muestra el correspondiente color de la paleta de colores, estos colores se enumeran desde el 1 hasta el 56.
  • m: Muestra el mes como numero sin ceros (1-12).
  • mm: Muestra el mes como numero pero con ceros (01-12).
  • mmm: Muestra el mes en abreviación (Ene, Feb.) 
  • mmmm : Muestra el nombre del mes por completo (Enero, Febrero).
     
  • mmmmm : Muestra la inicial del mes (E, F).
  • d : Muestra el día como número sin ceros (1-31). 
  • dd : Muestra el día como número con ceros (01-31).
  • ddd : Muestra el nombre del día como abreviación (Lun, Mar).
  • dddd : Muestra el nombre del día por completo (Lunes, Martes).
  • yy o yyyy : Muestra el año en dos dígitos (00-99) o cuatro (1900-9999).
  • h o hh : Muestra la hora como números sin ceros (0-23) o como números con ceros (00-23).
     
  • m o mm : Muestra los minútos como números sin ceros (0-59) o como números con ceros (00-59).
     
  • s o ss : Muestra los segundos como números sin ceros (0-59) o como números con ceros (00-59).
     
  • AM/PM : Muestra la hora en formato de 12 horas, si no se indica esta opción se muestra la hora en formato 24 horas.
     
Ejemplo general

€ #,##[verde];(0,00€)[Rojo];"Cero";"<"



Explicación:

En este formato contiene 4 secciones separadas por el signo de punto y coma y utiliza un formato diferente por cada sección:
  • En este formato se indica que los números (1ra sección) se escriben en verde, llevan el signo del euro adelante, no se muestran los ceros no significativos y solo se muestran 2 decimales (#.##).(€ #,##[verde])
  • Los números negativos se escriben en rojo, entre paréntesis con el signo detrás y con dos números siempre ((0,00€)[Rojo]).
  • Los valores cero se sustituyen por la palabra cero y los textos por la cadena nula, es decir que si en la celda hay texto, ese texto desaparecerá

Si te gustó, suscríbete al Feed RSS de Ideas de Excel  y recibe nuestras actualizaciones

164 comentarios:

  1. Hola!
    Man a ver si puedes ayudarme por favor
    Tengo varias celdas de excel con los siguientes valores:

    1.01
    1.02
    1.03
    1.1
    1.2
    1.3

    Lo que quiero es, en esa columna poner una condición para que todos los "0" (Ceros) aparezcan en blanco, osea, algo así como ocultarlos.

    Quiero que quede algo así:

    1.(Cero oculto)1 1. 1
    1.(Cero oculto)3 1. 3

    Cómo puedo hacer eso?

    ResponderBorrar
  2. Saludos, puedes usar la siguiente fórmula =SUSTITUIR( A!; “0”;””).
    Ten presente que A! es la referencia a la celda donde están los valores.
    Espero que te ayude esta solución. Feliz día

    ResponderBorrar
  3. Excelente articulo, gracias, me ayudo bastante

    ResponderBorrar
  4. Saludos
    Tengo este dato en varias celdas (5,5) y quiero brindarle otro formato (05,05), (10,05)... agregar un cero antes y después de la coma, si el numero es menor que 10.

    ResponderBorrar
  5. Albertone , por favor me puedes aclarar si esos valores corresponden a coordenadas cartesianas o son números decimales.

    Saludos!

    ResponderBorrar
  6. COMO HAGO PARA HACER LA SIGUIENTE SERIE EN EXCEL
    L1-14
    L2-14
    L3-14
    POR QUE CON AUTORRELLENO ME CAMBIA SOLO EL 14

    ResponderBorrar
  7. Buenas, tengo una celda con la fecha 15/12/2014, le apliqué el formato dddd dd/mm/yyyy y todo bien, cómo hago para que el nombre del día aparezca todo en mayúsculas?, y otra cosa, quisiera mostrarlo en dos líneas dentro de una misma celda, en la primera línea el nombre del día y en la segunda la fecha. Tengo Excel 2010, Windows 7, Configuración regional Español (Perú)

    ResponderBorrar
  8. hola!!!
    mi duda es la siguiente, el numero del IMSS esta compuesto de 11 digitos y un agregado que lleva letras pero quiero separarlos y no se como.
    Ejemplo, si introduzco 10 digitos quiero que se corriga asi: 3210860631=3210-86-0631
    si se introducen 11 asi: 32108606318=3210-86-0631-8
    si lleva letra asi: 321086063185M84PE=3210-86-0631-8 5M84PE
    pero no se como escribirlo porque me imagino llevara una función logica.
    gracias a quien pueda responder esto

    ResponderBorrar
  9. Hola genio, Ayudame, Necesito predeterminar puntos.
    Por ejemplo: yo escribo ABCD12345678, Y necesito que se ponga puntos, y que figure AB.CD.12345678. o sea, Que se ponga dos letras UN PUNTO otras dos letras OTRO PUNTO, y despues los 8 numeros. y si es posible que se pongan en mayuscula las letras sin que yo tenga activada la mayuscula...

    Como hago? Gracias.

    ResponderBorrar
  10. Hola buenos dias muchas gracias excelente tu informacion, una pregunta estimado amigo tengo una lista de registros de alumnos son como 2000 y cada uno de ellos tiene asignaturas diferentes, y deseo realizar lo siguiente;
    Hacer dos listas despegables dependientes, en la primera eliga el registro del alumno y en la segunda lista automaticamente me filtre las asignaturas y me deje elegir las asignaturas de ese alumno, he intentado nombrar mis asignaturas como nombre del registro, pero excel no permite nombrar rango por numero como le puedo hacer muchas gracias en adelanto

    ResponderBorrar
  11. Hola. Como hago para copiar una celda de texto que tiene una longitud variable, en otra celda que tenga una longitud fija de 30 caracteres, agregando espacios en blanco al texto original para completar los 30 caracteres?

    ResponderBorrar
  12. Por-fa lo he intentado y no consigo saber: intento meter numeros de cuentas bancarias que llevan dos ceros al principio y algunas llevan espacio y dos numeros finales . Cuando meto el numero de cuenta los tres finales me los pone a cero le meta la cantidad que le meta .. Y sin haber heco ningun esapcio porque yo no se lo he metido Porque ?

    ResponderBorrar
  13. COMO PUEDO PERZONALIZAR PARA QUE ME QUEDE LA CELDA CON 30 POSICIONES PERO EN TEXTO. EJ: MILLER SIGUIENTE CELDA

    ResponderBorrar
  14. Tengo valores en orden de miles como:

    2345.45

    Lo que quiero es formatear y que aparezca esta como:

    2,3

    Es esto posible? Pruebo con #,# y me aparece:

    2,345

    En que estoy mal?

    Gracias!

    ResponderBorrar
  15. @victor.zubiaga Podrías intentar con este formato personalizado #,0.

    ResponderBorrar
  16. http://ideasdeexcel.blogspot.com.co/2015/08/aplicar-formatos-personalizados-para.html

    ResponderBorrar
  17. Hola, muy bueno el artículo!

    Es posible definir un formato de celda que quite caracteres?
    Por ejemplo, quiero sustituir:
    "0. Texto" por "Texto"
    "1. Texto" por "Texto"
    "2. Texto" por "Texto"

    Mi idea es que el "#." siga escrito implicitamente en la celda, pero oculto a la vista. De esta manera poder definir formatos condicionales en base al prefijo "#." independientemente del texto que haya escrito despues de eso.

    Gracias!!!

    ResponderBorrar
  18. Hola yo deseo colorear los valores menores a 100% en rojo y los mayores en azul, es posible esto?

    ResponderBorrar
  19. Si claro, esto lo haces con la opción Validación que encuentras en excel en la pestaña Inicio - Grupo estilos - Formato Condicional - Resaltar reglas de celdas. Es mayor que... para los que desees mayores al 100% y es menor que.. para los otros...
    Debes primero seleccionar toda la columna que desees aplicarle la validación y nada mas.

    éxitos.

    ResponderBorrar
  20. hola necesito crear un formato que me permita ingresar numeros de la siguiente manera
    3.774.523-6
    lo hago en personalizado pero me pone de la siguiente manera
    37.745.23-6

    ResponderBorrar
  21. @mngpdu Indícanos que formato usaste para ayudarte mejor.

    ResponderBorrar
  22. Buena noche es urgente necesito ingresar en un formato de la dian un numero que tenga 30 caracteres ademas que el numero tenga formato texto pero me aparece asi: 1,982101E+29 y necesito que aparezca asi 198210100000000000000000000000
    que hago en la celda aparece asi pero en la parte de arriba aparece mal
    Gracias

    ResponderBorrar
  23. Hola Maira, lo que debes hacer es ampliar el ancho de la celda, saludos

    ResponderBorrar
  24. Necesito ayuda: Quiero poner en una celda un formato que me reconozca la unidad según millones y Billones, es decir que si en la celda A1 se coloca un Valor: A1: 13,596,225.12 se convierta automaticamente en A1: 13.59Mill. Pero que si el número es A1: 13,596,225,358.12 Se coloque automaticamente en 13.59Bill

    ResponderBorrar
    Respuestas
    1. Este comentario ha sido eliminado por el autor.

      Borrar
    2. Hola, lamento no tener una respuesta pero tengo la misma duda que tú. E revisado el muro y no la han respondido. Por favor alguien sabe como hacerlo.

      Borrar
    3. Hola, debes ir a formato de celdas, y escribir en la casilla Tipo, lo siguiente
      [<999950]#0,0." K";[<999950000]#00,00.." Mill";#00,00..." Bill"

      Borrar
  25. Hola necesito que en una celda se puedan poner numeros con un solo decimal, eh logrado poner la formula de validacion pero cuando pongo por ej XX,0 me da error en cambio si pongo xx,1 funciona, osea puedo poner cualquier numero menos algun num que sea ,0 o entero.Alguien me puede ayudar?Muchas gracias
    Saludos

    ResponderBorrar
  26. Hola, requiero agregar dos ceros a una cifra pero sin punto ni coma ni nada la cifra es 1234 y debe aparecer 123400. Desde ya mil gracias

    ResponderBorrar
  27. Hola, requiero agregar dos ceros a una cifra pero sin punto ni coma ni nada la cifra es 1234 y debe aparecer 123400. Desde ya mil gracias

    ResponderBorrar
    Respuestas
    1. Claro, debes ir a Formato de celdas, personalizada y agregar este formato #"00" con esto te colocará dos ceros al final de cualquier cifra que escribas en la celda. Espero sea de ayuda este aporte, saludos.

      Borrar
  28. Por favor como hacer éste formato personalizado: 0102-0120-93-0000012345 en una celda

    ResponderBorrar
    Respuestas
    1. Claro podrias intentar con este formato personalizado y me comentas si te es util
      ####_#####_###_###########

      Borrar
  29. tengo este numero 243840 y necesito quede como 24,3840 es decir con 4 decimales
    favor si ayuda en alguna formula, gracias

    ResponderBorrar
  30. BUENAS TARDES, COMO PUEDO AGREGAR ESPACIOS A UNA CELDA CON CONTENIDO, DE TAL MANERA QUE LA CANTIDAD DEL CONTENIDO TEXTO DE LA CELDA+ LOS ESPACIOS ES BLANCO AGREGADOS SUMEN 40 DE LONGITUD Y/O LARGO.... AGRADECERÉ SU APOYO

    ResponderBorrar
    Respuestas
    1. Gracias por preguntar.
      Para este ejemplo tengo en la celda A1 y B1 respectivamente las palabras IVAN y ALANIA2010, para agregar los espacios en blanco y que su cantidad de caracteres total sume 40 en la celda C1 y D1 respectivamente agrego la siguiente funcion,
      C1=A1&REPETIR(" "; 40 - LARGO(A1))
      D1==B1&REPETIR(" ";40-LARGO(B1))

      Con esto se obtiene el resultado que usted necesita.
      No olvides comentar si te es util

      Borrar
  31. EJEMPLO:
    IVAN+)36 ESPACIOS EN BLANCO), XQ IVAN TIENE 4 CARACTERES Y FALTAN 36 CARACTERES PARA 40... Y ASI EJEMPLO:

    ALANIA2010(+30CARACTERES/ESPACIOS EN BLANCO)



    ALGUNA FORMULA PARA COMPLETAR UN TEXTO EN EXCEL CON ESPACIOS EN BLANCO DE TAL MANERA QUE SUMEN 40 CARACTERES.


    AYUDA!!

    ResponderBorrar
    Respuestas
    1. Gracias por preguntar.
      Para este ejemplo tengo en la celda A1 y B1 respectivamente las palabras IVAN y ALANIA2010, para agregar los espacios en blanco y que su cantidad de caracteres total sume 40 en la celda C1 y D1 respectivamente agrego la siguiente funcion,
      C1=A1&REPETIR(" "; 40 - LARGO(A1))
      D1==B1&REPETIR(" ";40-LARGO(B1))

      Con esto se obtiene el resultado que usted necesita.
      No olvides comentar si te es util

      Borrar
  32. qué bueno que este post siga activo, porque tengo una duda.

    Cuando estoy en excel lo que quiero es que me aparezca un guión en lugar de cero, he hecho que pueda aparecer pero usando el formato de "aplicar miles", pero a eso no le puedo colocar el formato de alineado, solo se ajusta al lado de derecho y lo que quiero es que ese cero (el guión) me quede centrado.

    ¿Se podrá hacer?

    ResponderBorrar
    Respuestas
    1. Nos podrías enviar el formato personalizado que has usado para replicar el caso y poder ayudarte? o el ejemplo mas detallado.
      Quedo a la espera.

      Borrar
  33. claro... espero darme a entender, dejo el link para que lo vean:
    http://i222.photobucket.com/albums/dd253/k_x_100pre/Sin%20tiacutetulo_zpsvzpczckq.png

    ResponderBorrar
  34. Entendido, he usado el siguiente formato personalizado 0;-0;"-" con el cual coloca guion si la celda tiene cero, ademas me conserva la alineacion del texto que se tenga.

    Otra posibilidad es usar una funcion Si donde luego de una operacion matematica (por ej suma) pregunta si es cero coloca un guion, y si no deja el mismo resultado.
    =SI(A1+B1=0;"-";A1+B1)

    Espero haya sido de utilidad.

    ResponderBorrar
  35. Hola, como puedo hacer que las celdas ya escritas, por ejemplo "1234567" quede como "1.23.45.67" de forma automatica?

    ResponderBorrar
    Respuestas
    1. La celda siempre conservará los 7 caracteres del ejemplo?
      Si es afirmativa la respuesta, entonces lo podrías usar de esta manera. #"."##"."##"."##
      Debes ir a celda y formato personalizado.

      Borrar
  36. Buenos noches, tu publicacion me ha servido un monton.
    Sin embargo, quisiera que me ayudara con este problema que tengo. Necesito un formato personaliado que cuando un numero tenga decimales me muestre hasta 2 posiciones, pero si este numero no tiene decimales pues que no me muestre ninguno. Algo asi:

    1.23324 = 1.23
    1.3 = 1.3
    2 = 2

    Yo he probado con esto #.?? pero cuando el numero es entero me bota 2. y solo quisiera que me muestre 2
    Espero me puedas ayudar.

    Gracias de antemano

    ResponderBorrar
  37. En el grupo numeros, en el formato de contabilidad viene por default las monedas $, Euro, como cambio para que me salga otra moneda por defalut por ejemplo a L.

    ResponderBorrar
    Respuestas
    1. Hola lo debes realizar en Panel de control, configuración regional, configuración adicional, Moneda y en símbolo de moneda lo deberás cambiar, con esto también cambiará en excel.

      Borrar
  38. Buena Tardes.... Tengo una función buscarv() y me arroja un resultado en una celda determinada. Quisiera poder que esta celda que contiene el recultado muestre el siguiente formato "dos digitos y el texto transacciones". Por ejemplo: 03 transacciones

    ResponderBorrar
    Respuestas
    1. Buen día,

      Debes combinar dos funciones para traer el resultado como esperas, las funciones son =CONCATENAR(BUSCARV("20";Hoja1!F:I;3;0);" Transacciones")

      Donde dentro de la primera parte del concatenar realizarás la funcion buscarv, y en la segunda parte tendrás el texto transacciones.
      en este ejemplo tengo un listado en la hoja1, y en la hoja 2en la celda A1 uso el buscarV y el resultado será 03 transacciones. Coméntanos si logras el resultado que requieres.

      Borrar
  39. Por favor como hacer éste formato personalizado: 0102-0120-93-0000012345 en una celda.El que me distes no sirvió:####_#####_###_###########

    ResponderBorrar
    Respuestas
    1. Intenta este ###-####-##-########## en mi office 2016 funciona perfecto.

      Borrar
    2. Yo necesito este formato: 000-000000-0000L, es decir que la última letra yo la pueda cambiar con cualquier otra cuando ingresos el valor a la celda donde tengo ese formato

      Borrar
    3. Indicanos tu valor original para darle el apoyo.

      Borrar
    4. Necesito el mismo formato, por ejemplo "0011512850001E" pasarlo a "001-151285-0001E".

      Gracias

      Borrar
    5. Desde los formatos no es posible, la cantidad de caracteres siempre es el mismo??? De ser positiva, esta seria una opcion, con la siguiente formula: =CONCAT(IZQUIERDA(H46;3);"-";EXTRAE(H46;LARGO(H47)+1;6);"-";DERECHA(H46;5))
      La H46 es la celda donde tengo el valor incial.

      Borrar
    6. Lo que quería era ponerlo como formato de celda en una tabla. Puedo ponerlo, pero cuando agrego la letra al final se pierde el formato. De todos modos gracias por tu ayuda.

      Borrar
  40. Buenos días,

    ocupo ana función buscarv para buscar un valor cuando se repite la misma fecha y enviarlo a una celda ordenada, mi problema comienza cuando se repite una fecha más de una misma vez, y la funcion buscarv por defecto siempre me envía el primer valor y el segundo no puedo buscarlo.

    *Esta fórmula estoy ocupando

    =SI.ERROR(BUSCARV(AF3;$BQ$2:$CU$33;2;FALSO);0)

    ----------------------------------------------------
    Segunda consulta:

    *Quiero que una celda que tiene formato ddd-dd-mm-yyyy h:m quiero copiarla o enviarla con (=) a otra celda pero que ésta se pegue con formato dd-mm-yyyy, ya que sino la formula buscarv que te copie arriba no trabaja, por el momento lo soluciono copiandola control+c la pego en block de notas para sacarle el formato y la pego en la celda que necesito.

    ResponderBorrar
  41. Para la consulta 1: No existe una función que nos devuelva varios resultados con una sola consulta, si deseas Buscar un valor y regresar múltiples resultados en Excel debes usar un conjunto de funciones como si, k-esimo-menor o mayor, sierror, indice, entre otras.
    Para la consulta 2: Para copiar con formato, debes darle clic en copiar, y al pegar, pegado especial, Formato.
    saludos

    ResponderBorrar
  42. Buenas, yo necesitaba establecer un formato personalizado para los numeros de cuenta de los IBAN, pero he probado con ####" "####" "####" "####" "#### y no funciona, que hago mal?

    gracias.

    ResponderBorrar
  43. necesito que excel me tire por defecto un un numero en este formato 000-000-0000000 como lo hago?

    ResponderBorrar
    Respuestas
    1. Y la cantidad de dígitos siempre será 13? 3-3-7? de ser positiva su respuesta, puede usar el formato personalizado ###-###-#######, lo he probado en mi office 2016 y funciona correctamente.

      Borrar
  44. muy buenas noches, estoy haciendo un trabajo en los modelos de presupuestos y necesito resolver la siguiente situación:
    Tengo una celda que me calcula el tiempo de una operación en horas y he creado 3 celdas aparte que me dan el tiempo en min-hrs-días, luego necesito crear otra celda que sus condiciones me permitan cambiar la personalización y su valor en función de ests 3 celdas anteriores si cambia la 1era celda.
    Ej:
    tiempo operación en hrs: 0.67hr


    Tiempo estimado:39.96min 39.96min- 0.67hrs-0,09días
    En la celda de resultado tiempo estimado he escrito la siguiente formula: =SI(G49<1;E54;SI(G49>=1;F54;SI(G49>=7,5;G54)))
    y las celdas de tiempos en min-hrs-días esta´n personalizadas.
    pero no me resuelve cambiando la celda del tiempo estiamdo en función del tiempo de operación.

    Si el tiempo de operación fuera:
    Ej 2:
    Ej:
    tiempo operación en hrs: 1.33hr

    Tiempo estimado:1.33hrs 79.92min- 1.33hrs-0,18días

    Ej3:
    Si el tiempo de operación fuera:
    Ej 2:
    Ej:
    tiempo operación en hrs: 7.5hr

    Tiempo estimado:1día(s) 450.15min- 7.5hrs-1días.

    No se si me he hecho entender cualquier duda te puedes poner en contacto conmigo a través de jpalaui@yahoo.es.

    Muchas gracias.

    ResponderBorrar
    Respuestas
    1. Hola! te enviamos un correo para que nos hagas llegar el archivo, quedamos atentos

      Borrar
  45. Este comentario ha sido eliminado por el autor.

    ResponderBorrar
  46. Hola antetodo un saludo, bien tengo un problemita, estoy introduciendo notas de examen en una tabla excel y cuando por ejemplo pongo 5 me lo sale 5,0, quiero mantener que tenga un decimal en el formato para cuando tenga que poner por ejemplo 5,5 que puedo hacer, muchas gracias.

    ResponderBorrar
    Respuestas
    1. Hola Gracias por escribirnos, Debes dar clic derecho sobre las celdas y luego formato de celdas, una vez aquí en la categoria Numero deberás amopliar la posiciion decimales a 1 y ademas vas a personalizada y colocas en tipo 0,0 y aceptar. Con esto podrás ver el formato 5.5

      Borrar
    2. gracias Diego pero lo que quiero es que cuando sea 5,0 no aparezca el ,0 y cuando sea 5,5 si aparece. no se si habrá esa opcion.

      Borrar
    3. Claro, con el formato general de la celda en mi excel 2016 se obtiene el resultado que esperas, que si dígito 5 o 5,0 me muestra solo 5... en cambio si dígito 5,5 este si lo muestra completo.
      En caso de no funcionar así podrías hacer una función SI .. en la columna B tengo las notas
      =SI(DERECHA(B5;1)>0;B5;IZQUIERDA(B5;1))

      Borrar
    4. Muchas gracias, y perdona el retraso en los agradecimientos

      Borrar
  47. ¿Qué formato utilizo para obtener en una celda los siguientes valores?
    1,5 ----> 1,5
    2 ----> 2
    Si utilizo =TEXTO(A1;"0,#") los resultados son 1,5 y 2, (aparece una coma después del 2 y es lo que no consigo eliminar)

    ResponderBorrar
    Respuestas
    1. Hola buen día,
      Por favor intenta esta =SI(RESIDUO(A6;TRUNCAR(A6;0))=0;A6;A6)

      Borrar
    2. Continuo sin obtener lo que necesito, al poner un valor decimal me lo redondea. Gracias de antemano

      Borrar
    3. Hola, ingresa por favor a este video. https://www.visiontecnologica.com/images/stories/Descargas/Resolver19122016.swf

      Borrar
  48. Hola, tengo una celda B1 con 100012345 y C1 con 2500.56 y quiero obtener una celda
    E1 con 100012345002500.561 como podria obtener ese formato. entre B1 y C1 que se aumente 00 y al final el 1 espero haberme explicado gracias por su colaboracion.

    ResponderBorrar
    Respuestas
    1. En la celda D1 podrías usar esta función =B1&"00"&C1&"1"
      o también esta =CONCATENAR(B1;"00";C1;"1")
      con las dos obtienes el resultado que requieres.

      Borrar
    2. Gracias por esa respuesta, esa es la que sirve perfecto pero hay un detalle, en el caso que C1 sea 12000.56 el 00 deberia ser un solo 0, como resultado sería, 100012345012000.561 no se si se entendio o me explique. y es ahi donde se me rompe la cabeza.

      Borrar
    3. Claro y de que depende que sea uno o dos ceros? es para intentar hacerlo de otra manera.

      Borrar
    4. hola para empezar, disculpas por no seguir con la duda, los ceros dependen del monto, como en el caso anterior nos indicaba que es 2500,56 y en el siguiente ejemplo indica 12000,56 entonces disminuye un cero, si fuese 999,99 deberia ser 3 ceros, espero aun me puedan ayudar aun. gracias gracias.

      Borrar
    5. Si entiendo bien
      para 12000.56 serian un ceros
      para 2500.56 serian dos ceros
      para 500.56 serian tres ceros
      para 99.56 serian cuatro ceros
      para 9.56 serian cinco ceros
      Si esto es correcto y teniendo el mismo ejemplo rengo una celda B1 con 100012345 y C1 un valor y quiero obtener una celda
      Y sabiendo que el largo máximo de c1 es 8 y mínimo es 4 en al celda E1 podría tener esta función =CONCATENAR(B1;SI(LARGO(C1)=8;"0";SI(LARGO(C1)=7;"00";SI(LARGO(C1)=6;"000";SI(LARGO(C1)=5;"0000";"00000"))));C1;"1") Quedo atentos a sus resultados

      Borrar
  49. Hola Diego quiero validar que en una celda solo se pueda ingresar numeros en el siguiente formato 123,456,789.00 (separación de miles , y símbolo decimal .)ya que si quien ingresa los valores tiene la configuración regional diferente o si usa separacion demiles y simbolo decimal diferente el valor no lo puedo usar despues.

    ResponderBorrar
  50. Saludos, Tengo la siguiente fórmula en un hora de excel (=Datos!A16&" "&TEXTO(Datos!B16;"dd/mm/yyy")y efectivamente me trae el texto combinado con la fecha (Fecha: 31/01/2017), pero si abro la mismo hoja de excel en otra máquina me aparace "Fecha: 31/02/yyy", no entiendo porque en algunas máquinas me abre bien y en otras no.

    ResponderBorrar
    Respuestas
    1. Debe ser la configuracion regional o el idioma de los dos equipos donde abres el informe.

      Borrar
  51. Hola, felicitaciones por el post

    Agradezco si me puedes responder la consulta.

    Tengo el siguiente numero:003945

    Necesito convertirlo en: 00:39:45, son horas, minutos, segundos.

    Aguardo tus comentarios, saludos cordiales,

    ResponderBorrar
    Respuestas
    1. Hola Hector, Si tengo este valor 003945 en formato de celdas texto en la celda A1. Yo podría extraerlo
      B1= =IZQUIERDA(A1;2)
      B2= =EXTRAE(A1;LARGO(A1)-3;2)
      B3==DERECHA(A1;2)
      B4= =CONCATENAR(B1;":";B2;":";B3)

      Borrar
  52. Buenos días. Muy bueno el Post. Felicitaciones, estoy teniendo un problema con un trabajo.. Ejemplo.: estoy trabajando con muchas columnas que son la numeración de un libro. pero este tiene que mantener un formato de 0.000.000 pero cuando llego al 0.000.999 me agrega un decimal, necesitaría que sea 0.100.000 y no 0.000.1000
    Desde ya muchas gracias.

    ResponderBorrar
  53. Buenos días. Muy bueno el Post. Felicitaciones, estoy teniendo un problema con un trabajo.. Ejemplo.: estoy trabajando con muchas columnas que son la numeración de un libro. pero este tiene que mantener un formato de 0.000.000 pero cuando llego al 0.000.999 me agrega un decimal, necesitaría que sea 0.100.000 y no 0.000.1000
    Desde ya muchas gracias.

    ResponderBorrar
    Respuestas
    1. Hola, nos puedes hacer llegar el ejemplo a ideasdeexcel@visiontecnologica.com para darte la ayuda.

      Borrar
  54. Estimado. Buenos días.
    Necesito hacerle la siguiente consulta referente al formato de celdas por ejemplo quiero ingresar el valos 1.025 (el punto significa decimales), he probado todas las posibilidades que conozco y no hay forma de lograrlo ya que al ingresar este valor lo toma como 1025.00 obviamente no es lo que necesito, pero curiosamente si ingreso el valor 1.02 entonces si lo acepta como 1.02, espero su respuesta.
    Muchas gracias por su atención. Cordialmente Guillermo (gabeyer@hotmail.com / gabeyer@gmail.com)

    ResponderBorrar
    Respuestas
    1. Buen dia, por favor me indica su configuracion regional (panel de control-configuracion regional y de idioma) el simbolo de decimales o miles.

      Borrar
  55. Buenas tardes Diego como puedo hacer para que introducir un numero de cuenta en una celda (usando excel 2013) Ejemplo:1234-1234-12-1234567891,

    de antemano muchas gracias.

    ResponderBorrar
  56. como poner numeros negativos de color rojo ; teniendo mas de 4 formatos diferente de otro color..me dice el formato no puede usar numeros escritos?? u.u

    ResponderBorrar
  57. Buenas noches disculpa como le hago para realizar lo siguiente:

    Tengo 2 columnas que calculas porcentajes ejemplo:

    23.45% 76.55%
    50.00% 50.00%

    Utilice este formato 0.##% me arroja esto:

    23.45% 76.55%
    50.% 50.%

    Quiero que me quite tambien el punto no solo los decimales podrias atudarme de favor

    ResponderBorrar
    Respuestas
    1. Puedes usar este formato personalizado solo en los que estan quedando con la "," este #.0% ó #%

      Borrar
  58. Buenos días; veo en tu blog sobre excel y tengo una duda yo intento meter el número 04873215445000000612, pero me sale 4873215445000000000 a que es debido?? muchas gracias

    Pd: ya intenté lo de pasar a texto y otra vez a número y no resultó.

    ResponderBorrar
    Respuestas
    1. Hola buen dia, Es decir, las últimas cifras han sido reemplazadas por ceros!. Toda cifra después de la posición 15, será convertida a cero. Esto se debe a una limitación de Excel,
      Lo que debemos saber:
      - Excel tiene una limitación de precisión si el número excede las 15 cifras significativas
      - para introducir números de más de 15 cifras debemos convertirlos en texto, ya sea anteponiendo un ' o dando el formato Texto a la celda antes de introducir el número.

      Borrar
  59. Buenas Tardes. He visto tu generoso aporte y la deferencia con que respondes: FELICITACIONES Y GRACIAS.
    Ahora, a lo mío: Debo poner muchas notas (calificaciones), pero deseo escribir el número como entero, y que la celda me lo devuelva con un decimal Por ejemplo, escribir 55 y que la celda me devuelva 5,5. Probé una respuesta que das mas arriba (Propiedades Avanzadas); pero eso me lo aplica a todo el Excel. Yo solo deseo que la hoja /Específicamente algunas celdas), tengan esta propiedad o comportamiento.
    Gracias, espero jaber sido claro en mi solicitud

    ResponderBorrar
    Respuestas
    1. Hola muchas gracias,
      Imagino que los datos a ingresar oscilan entre 0 y 100. Para que luego automaticamente se conviertan en decimales. He realizado una pequeña macro, que podrás descargar en la ruta: https://www.visiontecnologica.com/Eventos/certificacionbiable/convertiradecimal.xlsm espero te sea útil. Aqui colocamos los datos de notas desde la A5 hacia abajo y al presionar el boton realiza el proceso.

      Borrar
  60. Buenas es posible crear formatos que digite ceros a la derecha, pero no fijos, por ejemplo se coloco 401, me represente 4.01.00.00

    ResponderBorrar
    Respuestas
    1. Hola, que cantidad de ceros va a la derecha? de que depende?

      Borrar
  61. Hola, que cantidad de ceros va a la derecha? de que depende?

    ResponderBorrar
  62. Hola, buen día, cuando pongo una cantidad me aparece 95,00 en lugar de 95.00 o me si quiero poner mil me aparece 1.000,00 como hago para que me aparezca la coma en lugar del punto y viseversa.
    Gracias, Saludos

    ResponderBorrar
    Respuestas
    1. Buen dia, por favor me indica su configuracion regional (panel de control-configuracion regional y de idioma) el simbolo de decimales o miles

      Borrar
  63. Buenas tardes, tengo un problema desde hace unos meses. El botón "000" hace que al cliquear en una celda con un número entero (o decimal) lo convierta en formato de número contabilidad, con lo que aparece el número con dos decimales. Si incremento o disminuyo los decimales el formato cambia a personalizado y aparecen las siguientes combinaciones 11111,0,0 11111,0,000 11111,0, 11111,, 11111, pero no consigo deshacerme de esta segunda coma (incluso pierde un "0" si el número termina así. He intentado cambiar formato de separador decimal, cambiar el formato de personalizado,... pero no consigo nada. Como indico hace unos meses todo funcionaba bien.
    La versión del MS excel es 2010 versión 14.0.7015.1000. Gracias por la ayuda

    ResponderBorrar
  64. Buen dia, al momento de poner el formato de 2.0 me queda el 2. como puedo quitarle el .

    ResponderBorrar
  65. Buen dia,

    Tengo una columna con series de numeros a los cuales quiero aplicarles un formato de tal manera que el resultado sea el siguiente:

    por ejemplo: en la celda F2 tengo 10154580001 y quiero que se muestre de la siguiente manera 1015.45.80.001

    ResponderBorrar
  66. buenos días, tengo una consulta en una planilla me vienen distintos números a los cuales necesito agregarle un punto después del segundo numero, el problema es que no siempre tienen la misma cantidad de caracteres, seria así en estos 3 ejemplos vienen asi:
    -32938806558255.
    -32470713.
    -329927271611.
    yo necesito que queden de esta manera:
    -32.938806558255
    -32.470713
    -32.9927271611
    desde ya muchas gracias

    ResponderBorrar
    Respuestas
    1. Hola Cristian NesCi.
      Realice esto que me planteas usando concatenar. y largo.
      Coloque los valores de ejemplo en las celdas A1, A2 y A3... Luego en la C1 C2 y C3 estas funciones
      C1= =IZQUIERDA(A1;2)&"."&DERECHA(A1;LARGO(A1)-3)
      C2==IZQUIERDA(A2;2)&"."&DERECHA(A2;LARGO(A2)-3)
      C3==IZQUIERDA(A3;2)&"."&DERECHA(A3;LARGO(A3)-3)

      Por favor valida si es lo que necesitas.

      Borrar
    2. Esta perfecto, con eso ya está solucionado, muchas gracias!!!!

      Borrar
    3. pregunta aparte no se si se podrá, me llega un csv con valores como los que te di en dos columnas distintas, los cuales corresponden a latitud y longitud, por eso necesitaba que tuviese el punto en la segunda cifra, la pregunta es, hay alguna manera de que haga la conversión directamente?

      Borrar
    4. Se podria realizando un procesamiento con Macros al archivo para que lo haga automatico.

      Borrar
  67. hola buen día tengo una duda, tengo un lector de codigo de barras, en la celda de excel lo muestra de la siguiente manera 2918221 25.5; el 29 es el código de producto, 18221 indica el lote y el 25.5 indica el peso en kg. Se puede personalizar la celda de tal forma que podamos desglosar las variantes en una columna diferente??
    gracias

    ResponderBorrar
    Respuestas
    1. Claro que se puede. Tengo uuna duda, La cantidad de caracteres es fija? es decir el lote por ejemplo siempre van a ser 5 digitos?

      Borrar
  68. Estimados compañeros me podrían ayudar con este formula miren tengo el siguiente caso:
    Tengo un número 10,850.20 y lo que quiero es concatenarlo y se convierta de la siguiente manera
    00000001085020
    Ahora logro hacer que me lleguen los ceros pero me lo manda así 000000010850.2
    la formula que estoy realizando es la siguiente
    CONCATENAR(REPETIR(0,15-LARGO($E6)),$E6))
    Me pudieran guiar con esto?.

    Gracias

    ResponderBorrar
    Respuestas
    1. Hola Raul, primeramente se tiene que cambiar el caracter en tu caso separador de decimal .(punto) por nada con esta formula
      SUSTITUIR(K14;".";"") --> aqui va en k14 tengo el valor con el separador, el punto es el caracter y por lo que lo quiero reemplazar.
      Asi quedaria la formula completa
      =CONCATENAR(REPETIR(0;15-LARGO(SUSTITUIR(K14;".";"")));SUSTITUIR(K14;".";""))

      si tienes inquietudes nos escribes a soporte@visiontecnologica.com

      Borrar
  69. COMO PUEDO REPRESENTAR UN NUMERO DE CÉDULA 4-877-866 Y ME CAMBIE LOS ESPACIOS POR CEROS

    ResponderBorrar
    Respuestas
    1. Buen dia, Cuando me dice "espacios" se refiere a los guiones? que quede así? 408770866?

      Borrar
  70. Tengo una duda, necesito el siguiente formato y no encuentro el modelo adecuado. Me podeis ayudar?
    ES11 2222 3333 4444 5555 6666

    Saludos

    ResponderBorrar
    Respuestas
    1. Es posible que este formato como tal no exista... pregunta: la cantidad de digitos de ese campo siempre es 22?
      Si es correcto. y si en la celda a1 tengo el numero con 22 caracteres podria hacer un concatenar y extrae =CONCAT(IZQUIERDA(A1;3);" ";EXTRAE(A1;LARGO(A1)-19;4);" ";EXTRAE(A1;LARGO(A1)-15;4);" ";EXTRAE(A1;LARGO(A1)-11;4);" ";EXTRAE(A1;LARGO(A1)-7;4);" ";EXTRAE(A1;LARGO(A1)-3;4))

      Borrar
  71. Hola necesito que me ayuden con el formato de horas de excel como ustedes saben excel puede manejar horas en este fomato.
    8:00 AM
    10:30 PM
    4:30 PM
    8:00 PM

    Pues yo necesito que las horas se me representen de esta manera

    8:00 am
    10:30 pm
    4:30 am
    8:00 pm

    no 8:00 p.m.
    ni 4:30 a.m.


    ResponderBorrar
    Respuestas
    1. en minusculas sin los puntos???

      Borrar
    2. Correcto mi amigo; en minúsculas sin los puntos. Tengo una celda con muchas horas y quiero que me aparezca así.

      Borrar
    3. Con el formato personalizado, lograremos borrar el punto, pero lo deja en mayuscula
      [$-es-ES]h:mm:ss AM/PM

      Borrar
  72. corrijo: tengo muchas celdas con muchas horas..

    ResponderBorrar
  73. Hola, mi consulta es. cual es la formula para que en una columna se digital 14 dígitos en caso contrario me indique error si agrego una de mas

    ResponderBorrar
  74. Claro, Vas a seleccionar las celdas a la que quieras aplicar la validacion y en la pestaña Datos Clic en Validacion de datos en
    Permitir - Cualquier valor lo cambias por
    longitud de texto .. En minimo y maximo estableces el valor 14 Listo. Aceptar.
    Cuando intentes agregar mas digitos te saldrá error.

    ResponderBorrar
  75. Hola!!!! Una consulta: no sé porqué Excel me abrió todos mis comentarios ocultos que tenía en un libro, ahora los estoy viendo todos, ¿será que hay alguna manera para ocultarlos todos al mismo tiempo?
    porque tengo más 200 de comentarios ocultos y no puedo estarlos ocultando uno por uno

    ResponderBorrar
    Respuestas
    1. Mira esta entrada https://ideasdeexcel.blogspot.com/2014/12/ocultar-todos-los-comentarios-de-una.html

      Borrar
    2. Muchas gracias!!! Duda más que resuelta!

      Borrar
  76. Hola tengo una consulta requiero que este formato
    43590402811054

    aparezca asi 4742-0402-811.012 pero no tengo la menor idea de como hacerlo , puedo hacerlo con los guiones pero al momento de colocar el punto ya no me deja. le agradezco su ayuda.

    ResponderBorrar
  77. Hola,
    Gracias por este derroche de sabiduría...
    ¿Es posible que despues de abrir un viejo archivo en excel, pueda definir un nuevo formato de texto para todo lo que escriba a partir de ahora?
    Gacias nuevamente

    ResponderBorrar
    Respuestas
    1. En archivo - Opciones - General puedes especificar tipo y tamañano de letra.

      Borrar
  78. Hola,
    Necesitaría saber cómo podría cambiar en excel el formato de números pequeños, como 0.0051, a la forma 5.1 (la idea sería eliminar la los ceros significativos iniciales para que no aparecieran en los ejes del gráfico)
    Muchas gracias de antemano.

    ResponderBorrar
    Respuestas
    1. Desde los formatos no es posible, lo que podrias hacer sera multiplicar por 1000 los numeros que tengan ceros a la izquierda. =SI(IZQUIERDA(A1;1)="0";A1*1000;A1)

      Borrar
  79. Muchas gracias, ha sido de gran utilidad!!

    ResponderBorrar
  80. De casualidad sabes como hago para que una fecha y hora quede sin el AM O PM AL FINAL Siempre lo muestra: 31/01/2019 14:00:00 p. m.

    ResponderBorrar
    Respuestas
    1. Hola, pero deseas que tenga hora militar sin el pm o am... dejame el ejemplo para intentar encontrar solucion.

      Borrar
  81. Hola, estoy en un grafica y quiero que para el numero 0,0000000000276 me salga 2,76 a secas y no 2,76E-12. Gracias de antemano

    ResponderBorrar
    Respuestas
    1. Y en los ejes en la configuracion de minimos y maximos como está definido? En esta parte no debe estat en E-12 para que lo cambie dentro del grafico.

      Borrar
    2. Si quito la E en los maximos y minimos del eje, los datos se desplazan demasiado hacia abajo

      Borrar
    3. Al serun nuemero tan pequeño es de esperar, tendrias que tomar los valores origianales, y convertirlo a miles o millones. Pero tendria que ser todos los datos previo a la grafica. Este valor 0,0000000000276 por ejemplo multiplicarlo por 100.000.000.000 para que de 2.76 y luego graficar. No hay otra manera.

      Borrar
  82. Yo quiero saber como pongo formato de número de teléfono con el prefijo entre paréntesis.

    ResponderBorrar
    Respuestas
    1. miralo en https://ideasdeexcel.blogspot.com/2019/07/mostrar-en-excel-numero-com-formato.html

      Borrar
  83. buenas que formula tengo que pasar pa ra tener el resultado
    3% = 5.00
    10% = 10.00
    25% = 25.00

    ResponderBorrar
  84. Buenas tardes tengo Formato de Banco Colpatria que no he logrado organizar facilmente

    ResponderBorrar
  85. Buenas noches...Tengo un dolor de cabeza con esto:
    cuando quiero poner mi formato condicional 0.00 al numero 9 por ejemplo, me deberia salir por 9.00 no?
    pero sale como 0.09 es decir es como si no reconociera al punto decimal para que haga la separacion, es como si estuviera poniendo 000, sin que se reconozca al punto,
    he revisado en los separadores del sistema y esta bien. No se si sea problema de que alguna configuracion este mal, porque en otro ordenador si sale como deberia ser. Heeelp

    ResponderBorrar
  86. Buenas como hago para que me crear este formato L-00000000-20, lo intente pero el ultimo CERO no lo toma en cuenta y lo sustituye por un numero. EJE L-00004522-20, pero lo cambia a L-00004522-25

    ResponderBorrar
  87. Hola quería hacer un formato de número para que siempre que ponga un número como por ejemplo: "1212000", automáticamente se anteponga "001-"

    ResponderBorrar
  88. Hola buenas tardes, excelente post.

    Necesito ayuda. Estoy construyendo una macro para luego convertir a .txt, pero hay columnas que llevan un número x de ceros como contenido y otras deben llevar ceros a la izquierda. He convertido en texto, pero deja de considerarme los ceros a la izquierda cuando escribo el contenido, y cuando le doy formato personalizado con el # de ceros que necesito me borra el formato de texto. Ya no sé que más puedo hacer.
    Agradecería cualquier ayuda...Gracias

    ResponderBorrar
    Respuestas
    1. Hola Nany, Podrias primero usar la funcion Largo, para la cantidad de caracteres, y cuando no cumpla esta cantidad, hacer una funcion Si para rellenar los ceros a la izquierda. tambien comilla sencilla le concatenaria al inicio.

      Borrar
  89. Hola. Una pregunta tonta:¿Este post de Abril de 2010 que aún sigues contestando el 14 de Octubre de 2020, no ha recibido ningún premio o mención especial de Microsoft, Google, la OCDE, la UE, Mercosur, la ONU, o similar?. Increíble!. De todas maneras, ¿tienes la referencia de la documentación oficial completa de Microsoft para el tema de los formatos?. La que sale en las paginas oficiales de microsoft (como hacer, docs.microsoft, etc) es pobre, triste e incompleto.

    ResponderBorrar
    Respuestas
    1. Buen dia Jose, a pesar que el post sea de 2010, hay temas en excel que valen la pena compartir, y aun más responder, dado que es muy poca la documentacion. Nosotros en Vision tecnologica SAS, creemos que podemos optimizar el tiempo usando los recursos de excel de manera adecuada.
      La fuente oficial es https://support.microsoft.com/es-es/office/crear-un-formato-de-n%C3%BAmero-personalizado-78f2a361-936b-4c03-8772-09fab54be7f4
      Gracias.

      Borrar
  90. Hola, tengo una celda con este valor 15%, y quiero cambiar el % por pp., o sea, que lo cambiara por pp. Gracia.

    ResponderBorrar
  91. Quería decir que lo cambiara por 15pp, donde antes ponía 15% que ahora ponga 15pp.

    ResponderBorrar
  92. Hola Una pregunta
    como puedo poner el valor que esta en numero
    18558389
    a 185,583.89

    Si me podrian auxiliar por favor

    ResponderBorrar
  93. Respuestas
    1. Buen dia Carlillos, Puedes introducir los datos numéricos / letras en dos celdas anexas y utilizar una tercera para concatenar ambas cadenas o si vienen tal cual la escribes, podrias usar izquierda derecha para separar dependiendo de la cantidad de caracteres, y luego concatenar
      A1=281-310885-0002
      A2=W
      A3=A1&A2

      Borrar
  94. Hola, he probado mil maneras y no soy capaz. Intento hacer una hoja excel en la que cualquier número que pegue de otra hoja que venga en negativo, en mi hoja, sea automáticamente positivo. Celda a celda soy capaz por el formato personalizado pero una hoja completa en la que los numeros negativos se transformen en positivos y los positivos se queden positivos no soy capaz. Pueden ayudarme?
    Saludos
    Luis

    ResponderBorrar