lunes, 5 de noviembre de 2012

Extraer valores de un alfanumérico y sumarlo en Excel.

Veremos una matricial que nos ayudará a poder sumar 'valores' con una parte de texto añadida, es decir, alfanuméricos.
Daré respuesta a una lectora del blog que planteaba esta cuestión:


...no se sumar una serie de números con texto,en este caso kilómetros:
789 km
589 km
698 km...

Aquí el problema es claro, y es que al contener valores numéricos junto a una parte de texto, Excel reconoce la celda como de 'texto', lo que la convierte en inoperable... es como si quisieramos sumar palabras.
Veamos el planteamiento:

Extraer valores de un alfanumérico y sumarlo en Excel.


El trabajo para llegar a obtener el sumatorio de ese rango de celdas 'de texto', consistirá en primer lugar eliminar la parte de texto, que coincide en todas nuestras celdas con ' km'. Ojo, por que estamos eliminando también el espacio en blanco entre el número y el texto 'km'.
Lo que quede de esa eliminación, que conseguimos con la función SUSTITUIR, la convertimos en número, aplicándole la función VALOR. Al resultado numérico obtenido ya es posible aplicarle la función SUMA.
Si trabajamos sobre todo el rango al tiempo, de forma matricial, nuestra fórmula queda entonces:
=SUMA(VALOR(SUSTITUIR(A1:A3;" km";"")))

Extraer valores de un alfanumérico y sumarlo en Excel.


Con lo que obtenemos el resultado de sumar únicamente la parte numérica de cada cela.

8 comentarios:

  1. Hola soy Julio y yo tengo un problema parecido que quisiera ver si se puede hacer, trabajo con una lista de turnos en la que constan los turnos realizados en el mes, a la vez hay distintos tipos de turnos ej: TO= turno de 7 hs, T= turno de 24 hs, TN= turno de 18 hs. al final del mes se suman los turnos para computar las hs. mensuales. La pregunta del millon como hago para que sume todos los turnos según las letras, se podrá?

    ResponderEliminar
    Respuestas
    1. Hola,
      por lo que expones, necesitas saber cuántos turnos TO, T ó TN ha habido en el mes, por lo que parece que necesitas una función CONTAR.SI para cada turno, es decir:
      =CONTAR.SI(rango;"TO")
      =CONTAR.SI(rango;"T")
      =CONTAR.SI(rango;"TN")
      siendo 'rango' las celdas de todo el mes donde se distribuye esa codificación.
      Espero haberte entendido.
      Slds

      Eliminar
  2. Hola, me llamo juan y tengo un problema, sé que necesito esta fórmula y alguna más pero no doy con la solución, haber si pudieras ayudarme.
    Tengo una columna con:
    drop
    Rune
    Rune Piece x12
    Symbol of Harmony x5
    Rune
    Rune
    Rainbowmon 2
    Rune
    Rune Piece x8
    Yo quiero en esta columna buscar por ejemplo Rune Piece x y extraer el numero que tiene delante y sumarlos todos, podrias ayudarme?
    Muchas gracias de antemano

    ResponderEliminar
    Respuestas
    1. Hola,
      lo publicado en esta entrada, con una mínima modificación te servirá:
      http://excelforo.blogspot.com.es/2015/12/vba-una-funcion-para-extraer-caracteres.html
      Saludos cordiales

      Eliminar
    2. Gracias por tu respuesta.
      He hecho la funcion para extraer el numero de una celda modificando esta, como puedo ahora recorrer toda la columna e ir sumando los numeros? Ademas antes tengo que saber que esa celda si tengo que sumarla comparando con Rune Piece x?? antes de extraer el numero.
      He intentado algo asi pero no funciona:
      =SUMAR.SI(I:I;SI(IGUAL(Rune Piece x??; ExtraeTexto2(I:I));ExtraeNum(I:I);0))
      Las funciones de extraer son:

      Function ExtraeNum(celda As Range) As String
      Dim num As Integer
      'recorremos cada caracter
      For i = 1 To Len(celda.Value)
      'evaluamos si está entre A y Z
      If Mid(celda.Value, i, 1) Like "[0-9]" Then
      'en caso afirmativo concatenamos con resultados anteriores...
      num = num & Mid(celda.Value, i, 1)
      End If
      Next
      'finalmente devolvemos los caracteres textuales...
      ExtraeNum = num
      End Function

      Function ExtraeTexto2(celda As Range) As String
      Dim txt As String

      'recorremos cada caracter
      For i = 1 To Len(celda.Value)
      'evaluamos si está entre A y Z
      If Mid(celda.Value, i, 1) Like "[A-Z]" Then
      'en caso afirmativo concatenamos con resultados anteriores...
      txt = txt & Mid(celda.Value, i, 1)
      End If
      Next
      'finalmente devolvemos los caracteres textuales...
      ExtraeTexto2 = txt
      End Function

      Eliminar
    3. Hola,
      simplifica..
      en una columna adyacente aplica la función para extraer los valores numéricos, y en otra celda, donde necesites el resumen:
      =SUMAR.SI(A:A;"Rune Piece*";"B:B")
      donde en A:A estaría los códigos originales y en B:B el número extraido
      Saludos

      Eliminar
    4. Gracias, lo he conseguido. Habría alguna manera sin necesidad de usar otra celda para extraer los numeros?

      Eliminar
    5. bueno... desarrollando un procedimiento (macro)...

      Eliminar