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...

51 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
    6. Yo tengo el problema pero al contrario, necesito convertir los números de un rango a texto, no a letras sino a formato texto?
      Saludos.

      Eliminar
    7. Hola Gabriel,
      bastaría que añadieras delante un apostrofe:
      Range("f" & ff).Value = "'" & Range("f" & ff).Value
      Saludos

      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
  18. Buenos días estoy haciendo un formulario donde ingreso en un combobox un numero y me registra en la hoja de calculo formato texto, necesito que dicho registro se reconozca como numero, he leido muchas soluciones, pero mi ignorancia en el tema no me permite avanzar. Al ver los códigos, no se donde ingresarlos estoy perdido y desesperado.

    ResponderEliminar
    Respuestas
    1. Hola Raúl,
      depende de cada caso..pero podría valer aplicar la función Val.
      Por ejemplo:
      Range("A1").value=Val(Combobox1.Value)

      Pero como te digo depende de muchos factores...
      Prueba y comenta (especifica cómo es el número.. si tiene decimales, etc).

      Saludos

      Eliminar
    2. Estimado, muchas gracias por su respuesta. Pero no me funciona, ingrese tal cual el codigo (cambiando el rango a modificar de la forma "Q:Q") en la interfaz del form, pero no me resulta no registra y se demora en compilar. Son numeros enteros los que necesito cambien de texto a numero. Gracias

      Eliminar
    3. Hola,
      si introduces en el ComboBox valores enteros y los trasladas de alguna forma a la hoja de cálculo y te aparecen como texto, y no te funcione aplicando la función VBA de Val, prueba cambiando el formato de la celda directamente en la hoja a formato de Número...
      Es raro que en un número entero lo trasforme a texto ¿¿??

      Slds

      Eliminar
  19. Alguien me podria ayudar a mejorar esta macro porfavor

    Sub VALORNUMERO()
    Dim celda As Range
    Application.StatusBar = "Convirtiendo celdas seleccionadas a formato de número..."
    For Each celda In Selection
    'Se evalua cada celda del rango y se hace la conversión
    celda.Value = CStr(celda)
    Next celda
    Application.StatusBar = False
    Application.DisplayAlerts = False
    ThisWorkbook.Close
    End Sub

    saludos.

    ResponderEliminar
    Respuestas
    1. Hola
      si quieres convertir celdas con números almacenados como texto a número emplea la macro del post... ;-)
      En todo caso, en tu ejemplo, si empleas
      celda.Value = CStr(celda)
      estás pasando cada celda a tipo String/Texto...
      en todo caso
      celda.Value = CDbl(celda)

      Saludos

      Eliminar
    2. Muchas Gracias
      Saludos

      Eliminar
  20. Tengo una duda quisiera hacer un macro en un boton que al presionarlo me de el numero 2 en la celda A1 y al volverlo a presionar en la celda A2 me de otra vez dos, pero apartir de la celda A3 me comience a sumar la celda de arriba, y asi sucesivamente hasta la celda A35

    ejemplo
    CELDA A1
    2
    2
    4
    6
    10
    16

    ETC...

    ResponderEliminar
    Respuestas
    1. Hola,
      qué tal estás?, un placer saludarte
      determina cuál es la ultima fila donde añadir ese dos o la suma con alguno de estos métodos
      https://excelforo.blogspot.com/2017/03/vba-Localizar-Ultima-Fila-Excel.html
      y luego condiciona con IF THEN si la fila es >=3 para sumar los dos previos o añadir el dos

      Saludos

      Eliminar
    2. es que yo lo que quiero es que al presionar el boton sume apartir del segundo de la segunda celda el numero de la celda de arriba, por eso era el ejemplo de los consecutivos, pero necesito saber como hcerle para que lo haga con el boton, encontre como hacerlo con formula pero me saca la suma de todos al mismo tiempo de darle click al boton siendo que necesito que sea de uno en uno al ir haciendo click al boton.

      Eliminar
    3. ESTE ES EL CODIGO QUE GENERE GRABANDO EL MACRO HACIENDOLO MANUAL

      Sub Suma_acumulada()
      '
      ' Suma_acumulada Macro
      '

      '
      ActiveCell.FormulaR1C1 = "=1+1"
      ActiveWindow.LargeScroll Down:=1
      Range("A43").Select
      ActiveWindow.LargeScroll Down:=-1
      Range("A3").Select
      ActiveCell.FormulaR1C1 = "=R[-1]C"
      Range("A4").Select
      ActiveCell.FormulaR1C1 = "=R[-1]C+R[-2]C"
      Range("A5").Select
      ActiveCell.FormulaR1C1 = "=R[-1]C+R[-2]C"
      Range("A6").Select
      Selection.FillDown
      Range("A7").Select
      Selection.FillDown
      Range("A8").Select
      Selection.FillDown
      Range("A9").Select
      Selection.FillDown
      Range("A10").Select
      Selection.FillDown
      Range("A11").Select
      Selection.FillDown
      Range("A12").Select
      Selection.FillDown
      Range("A13").Select
      Selection.FillDown
      Range("A14").Select
      Selection.FillDown
      Range("A15").Select
      Selection.FillDown
      Range("A16").Select
      Selection.FillDown
      Range("A17").Select
      Selection.FillDown
      Range("A18").Select
      Selection.FillDown
      Range("A19").Select
      Selection.FillDown
      Range("A20").Select
      Selection.FillDown
      Range("A21").Select
      Selection.FillDown
      Range("A22").Select
      Selection.FillDown
      Range("A23").Select
      Selection.FillDown
      Range("A24").Select
      Selection.FillDown
      Range("A25").Select
      Selection.FillDown
      Range("A26").Select
      Selection.FillDown
      Range("A27").Select
      Selection.FillDown
      Range("A28").Select
      Selection.FillDown
      Range("A29").Select
      Selection.FillDown
      Range("A30").Select
      Selection.FillDown
      Range("A31").Select
      Selection.FillDown
      Range("A32").Select
      Selection.FillDown
      Range("A33").Select
      Selection.FillDown
      Range("A34").Select
      Selection.FillDown
      Range("A35").Select
      Selection.FillDown
      Range("A36").Select
      End Sub

      Eliminar
    4. BUENO CREO QUE ENCONTRE MI RESPUESTA PARA AQUELLOS QUE LO REQUIERAN AQUI ESTA EL APORTE

      Sub ACU()
      Hoja1.Cells(2, 1) = "2"
      Hoja1.Cells(3, 1) = "2"

      For I = 4 To 35
      Hoja1.Cells(I, 1) = Hoja1.Cells(I - 2, 1) + Hoja1.Cells(I - 1, 1)
      Next

      End Sub

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.