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:
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:
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.
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 '♦%' ")),"♦","")