miércoles, 19 de mayo de 2010

Macro para convertir en número valores de texto.

Días atrás comenté cómo desde los menús de Excel podíamos CONVERTIR EN NÚMERO VALORES ALMACENADOS COMO TEXTO; en esta ocasión generaré una sencilla macro en VBA para realizar esta conversión: multiplicar las celdas por UNO.
El código planteado surge de la cuestión lanzada por un lector:

...tengo la necesidad de crear una macro para convertir valores de texto a numérico, y evitar tener que hacerlo manualmente cada vez que uso la base de datos que me llega; los datos están siempre en la columna F pero la cantidad de filas es variable...


Mi hipótesis de trabajo es que los valores con formato de texto están siempre en la columna F, y que tiene una cabecera o título, es decir, que los importes empiezan siempre en F2, y nos da igual donde acaben (haya o no celdas vacías entre los valores).

El sencillo código VBA generado consiste en identificar las celdas con valor de la columna F y multiplicarlas por 1, de tal forma que se conviertan en celdas con valores numéricos para poder operar con ellas...

'para convertir valores almacenados como texto en números automáticamente
Sub Conv_text_Num()
Dim finAs Range
Dim f,ffAs Integer
Const x = 1
Set fin = Range("F"&Rows.Count).End(xlUp)
f = Cells(fin.Row, 6).Row
For ff = 2 To f
If Range("f" & ff).Value <> "" Then _
Range("f" & ff).Value = Range("f" & ff).Value * x
Next
End Sub



Luego, para ejecutarlo, lo más sencillo es crear un botón y asignádole dicha macro.
Como siempre en estos temas quedo expuesto a los comentarios de verdaderos expertos en programación VBA...

37 comentarios:

  1. se podria simplificar un poco y acelerar tambien.

    'para convertir valores almacenados como texto en números automáticamente
    Sub Conv_text_Num()
    Dim f, ff As Integer
    Const x = 1

    Application.ScreenUpdating=False
    fin = Range("F" & Rows.Count).End(xlUp).Row
    For ff = 2 To f
    If Range("f" & ff).Value <> "" Then _
    Range("f" & ff).Value = Range("f" & ff).Value * x
    Next
    Application.ScreenUpdating=True
    End Sub

    Saludos

    ResponderEliminar
  2. Y como hacer lo contrario? colocar automáticamente el apóstrofe de almacenado como texto, a una lista que no lo tiene.

    ResponderEliminar
  3. Colocar un apóstrofe, en celdas con valores numéricos, para guardarlo como texto desde luego sería una opción; pero creo que sería más rápido y práctico cambiar el formato de las celdas como 'Texto'.
    Slds

    ResponderEliminar
  4. Se puede mejorar algo,

    Dim Rango as range, Cadacelda as variant
    set Rango=Range("F1:F65536")
    For each Cadacelda as Rango
    Cadacelda.formulalocal=cadacelda.Formulalocal
    next

    ResponderEliminar
  5. Muchas gracias...
    todos los días aprendo algo nuevo.
    Tomo nota de la propiedad .formulalocal
    Slds

    ResponderEliminar
  6. Sub Conv_text_Num()
    'x Elsamatilde
    'seleccionar el rango de datos de la hoja o toda la hoja con Cells.Select
    Hoja3.Range("h7:h65536").Select
    For Each cd In Selection
    On Error Resume Next

    'si Val devuelve 0 es porque se trata de celdas con texto, no nros guardados como texto

    If Val(cd) <> 0 Then
    cd.Value = cd.Value * 1
    End If
    Next
    MsgBox "Datos Actualizados Satisfactoriamente"
    End Sub

    ResponderEliminar
    Respuestas
    1. excelente solucion, aplique otras soluciones incluso las que recomienda microsoft, ninguna funciono, pero esta macro resulto perfecta. gracias por tu aporte

      Eliminar
    2. Hola hucal55,
      como ves, en esencia la solución es siempre la misma: multiplicar por UNO el valor.
      Un cordial saludo

      Eliminar
    3. te colocas en la casilla de la forma que te apetezca (cualquiera de las anteriores vale) y ejecutas la siguiente linea
      ActiveCell.Value = ActiveCell.Value * 1

      Eliminar
    4. Efectivamente Francisco...
      la solución parece apuntar siempre en la misma dirección: multiplicar por uno.

      Saludos cordiales

      Eliminar
    5. Muuuchas gracias, respuesta sencilla y concreta, muy util.

      Eliminar
  7. como hago para que una lista de numero tenga un orden desendente y ascendete

    ResponderEliminar
    Respuestas
    1. Hola!!
      no queda claro si te refieres a una lista en la hoja de cálculo, o una lista mediante macros o una lista en un formulario.
      Para el primer caso deberías usar algún tipo de orden con la función K.ESIMO.MAYOR o K.ESIMO.MENOR, combinado con INDICE.
      Y en los casos posteriores con la instrucción SORT.
      Un saludo

      Eliminar
  8. Hola a tod@s y gracias por vuestras enseñanzas a estos humildes novatos entre los que me cuento.

    Tengo un código que si no le molesta a los administradores, lo pongo aquí para compartir, que encontré en un Blog y que va correctamente para cambiar de números a texto, pero por motivos de trabajo, a veces comparto en presentaciones también números en texto y me gustaría hacer la conversión de nuevo de texto a números, ¿si alguien sabe?, y por otro lado, ¿se le podrían incorporar a este código dos texbox para conversión a letras o números y que las letras fueran concatenadas saliendo en forma de texto?.

    Saludos y os paso el código.

    http://vba-para-excel.blogspot.com.es/2011/08/convertir-numeros-letras-con-decimales.html?showComment=1332939572795#c3508400716440551952

    ResponderEliminar
    Respuestas
    1. Hola, y gracias a ti por el enlace...
      efectivamente hay bastantes códigos que consiguen (algunos son muy buenos) pasar números a texto...
      sin embargo, no recuerdo y, sinceramente, me parece harto complicado, convertir un texto de una cantidad en un valor numérico... seguramente un especialista en programación pueda construir una macro que conjugue todas las posibilidades existentes...
      Un saludo

      Eliminar
  9. Hola, como puedo convertir una columna de una hoja que tiene emails a texto de Word necesito pegar un correo tras de otro separado de una coma y un espacio en un servidor de correos que admite esta forma:
    paulina.ros@uv.es, jose.rodriguez@uv.es, jauan.soler@uv.es
    Gracias de antemano.
    Luis

    ResponderEliminar
    Respuestas
    1. Hola Luis,
      necesitaría saber cómo tienes listada la columna ¿sólo los nombres o los email completos??
      Entiendo que en la columna tienes en cada fila un correo completo, por lo que podrías aplicar lo explicado en esta entrada:
      http://excelforo.blogspot.com.es/2011/01/vba-concatenar-elementos-de-una-lista.html
      Espero te sirva
      Slds

      Eliminar
  10. Excelente me sirvio mucho. Gracias

    ResponderEliminar
  11. Buénisima la aportación del anónimo con fecha 29 de noviembre.

    Resuelve de forma muy eficiente y rápida!

    ResponderEliminar
  12. SALUDOS ME SIRVIO MUCHO, BUEN RAZONAMIENTO AMIGO...

    ResponderEliminar
    Respuestas
    1. Gracias a todos!!
      el aporte del Anónimo del 29 de noviembre:
      Sub macro1()
      Dim Rango As Range, Cadacelda As Variant
      Set Rango = Range("A1:A10")
      For Each Cadacelda In Rango
      Cadacelda.FormulaLocal = Cadacelda.FormulaLocal
      Next
      End Sub
      es otra solución muy buena.
      Slds

      Eliminar
    2. Excelente aporte!! muchas gracias!!

      Eliminar
  13. Esta solo seleccioa los datos que hay en la columna (le regla es que no hayan celdas vacias en la columna)

    Dim CEDULAS As Range, N As Variant
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Set CEDULAS = Selection
    For Each N In CEDULAS
    N.FormulaLocal = N.FormulaLocal
    Next

    ResponderEliminar
    Respuestas
    1. Gracias Diego,
      en esencia es el mismo caso que el aplicado en comentarios anteriores, es decir, aplicando .formulaLocal

      Un saludo!!

      Eliminar
  14. Tengo este codigo

    ActiveCell.Offset(0, 2) = IIf(IsNumeric(textoPesoPorUnidad), FormatNumber(Replace(textoPesoPorUnidad.Value, ".", ","), 2), "")
    ActiveCell.Offset(0, 3) = IIf(IsNumeric(textoPesoPorBancal), FormatNumber(Replace(textoPesoPorBancal.Value, ".", ","), 2), "")

    ActiveCell.Offset(0, 2).FormulaLocal = FormatNumber(ActiveCell.Offset(0, 2).FormulaLocal, 2)
    ActiveCell.Offset(0, 3).FormulaLocal = FormatNumber(ActiveCell.Offset(0, 3).FormulaLocal, 2)


    En un formulario:
    los datos ingresados son:
    textoPesoPorUnidad = 25
    textoPesoPorBancal = 1000

    En las celdas lo almacena 25 y 1.000,00
    lo convierte en numeros pero no mantiene el formato que deseo en el caso de 25, por el resto anda bien.
    Me puedes ayudar.






    ResponderEliminar
    Respuestas
    1. Hola, que tal?
      creo es más práctico aplicar la propiedad .NumberFormat, algo de este estilo:
      ActiveCell.Offset(0, 3).NumberFormat = "#,##0.00;-#,##0.00"
      La función VBA FormatNumber es algo más compleja de aplicar.

      Saludos

      Eliminar
  15. buenas tardes. soy nuevo en esto de las macros, pero necesito una macros que me permita calcular el valor en su minima expresion de un nombre al introducirlo.
    ejemplo:
    maria =41991, 4+1+9+9+1=24; 2+4=6
    entonces el valor numerico de maria=6

    ResponderEliminar
    Respuestas
    1. Hola Edgar,
      quizá está fórmula matricial te sirva:
      =SUMA(INDICE(1*(EXTRAE(B2;FILA(INDIRECTO("1:"&LARGO(B2)));1));;))
      suponiendo en B2 el número 41991,
      sobre esa fórmula tendrás que volver a aplicarla sucesivamente...

      En todo caso, escribiré un post al respecto.

      Saludos

      Eliminar
  16. Hola, gracias por compartir tu conocimiento. Trataba de seguir las diferentes soluciones adaptada a mi problema, pero no puedo dar con el asunto. Mi caso es que tengo un formuilario con datos que surgen de diferentes textbox, y por medio de un boton "registrar" los voy volcando como si fuese una base de datos a la hoja8. Columnas enteras de textos, columnas enteras de numeros...y bueno en el caso de las q contienen numeros, me encuentro mismo con este error.....cuál sería la solución y desde donde la ejecuto...Gracias :)

    ResponderEliminar
    Respuestas
    1. Hola,
      el problema de pasar números desde un textbox a la hoja de cálculo es que el sistema decimal empleado (y de separadores de miles) en VBA y en nuestras hojas de cálculo es diferente, por eso se suele emplear en VBA el método Replace para cambiar el punto decimal de VBA por la coma de la hoja.
      Algo así:
      Replace(textbox1.value, ".", ",", 1)
      así luego se puede devolver a la hoja de cálculo

      Espero haberte orientado
      Slds

      Eliminar
    2. Ah bien, creí q podría adaptar a mi caso alguna de las macros mencionadas, pero bueno, t agradezco por el dato, lo voy a tener en cuenta. Gracias por contestar. :) Saludos Ismael.

      Eliminar
  17. Hola Ismael. Muchas gracias por este código. Muy útil.

    ResponderEliminar
    Respuestas
    1. Hola Ismael. Tu me puedes colaborar con este tema. Estoy aplicando tu código de convertir de texto a numero. Pero me lo deja en notación científica. Así 799552E+11. Me colaboras por Favor!!!!

      Eliminar
    2. Hola Carlos,
      parece es un tema exclusivo de formato, está tomando el formato de número científico
      Para solucionarlo podrías añadir una nueva linea de código debajo de:
      Range("f" & ff).Value = Range("f" & ff).Value * x
      Range("f" & ff).Numberformat="0"

      Debería ser suficiente
      Saludos

      Eliminar
    3. Hola Ismael. Perfecto, eso era. Muchas Gracias.

      Cordial Saludo

      Eliminar