Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

146
Visualizações
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 Respostas
Responde à pergunta

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 Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda