Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

167
Vistas
Google sheets - Extract numbers with their units of measurment seperated by a blank space e.g "20 km"

I have a script for a custom function that extracts numbers with their units of measurment:

/**
 * @customfunction
 */
function EXTRACTMEASUREMENT(input) {
  // match all words which starts with a number
  var result = input.match(/\d+[a-zA-Zα-ωΑ-Ω]+\S*/g)
  // combine array into a string separated with spaces
  result = result.join(' ');
  // Remove special characters(except whitespace) in the string
  result = result.replace(/[^a-zA-Zα-ωΑ-Ω0-9\s]/g, '')
  return result;
}

For example, i have a text in H42 and i put the function in I42. It works like that:

enter image description here

output

But the thing is that, if I put in H42 a number but followed by a space and then the unit of measurment, the function doesnt detect it and as a result, it doenst even extract it. See example:

enter image description here

As you can see, the "12 kg" and the "1 L" both have a blank space between. So the function only extracts "50gr" which isnt seperated by a blank space.

What can I do so the custom function returns the numbers with their units of measurment even if they are seperated by a blank space? I want it to return

12kg 1L 50gr

just like the 2nd attached image.

I am just a begginer in these so I don't know how to write code. It would be really helpful if you could make the proper modifications to my script and then paste it in the answers.

about 4 years ago · Juan Pablo Isaza
1 Respuestas
Responde la pregunta

0

Try

=arrayformula(substitute(transpose(query(flatten(split(
REGEXREPLACE(A1,"([0-9.,/]+[ ]{0,1}[a-zA-Z1-3/.\-""]+)","♣♦$1♣")
,"♣")),"select * where Col1 like '♦%' ")),"♦",""))

or with result in one cell

=substitute(textjoin(" - ",true,query(flatten(split(
REGEXREPLACE(A1,"([0-9.,/]+[ ]{0,1}[a-zA-Z1-3/.\-""]+)","♣♦$1♣")
,"♣")),"select * where Col1 like '♦%' ")),"♦","")

enter image description here

about 4 years ago · Juan Pablo Isaza Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda