Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

148
Views
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 answers
Answer question

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 Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!