Find a number in a String between 2 numbers

English
#1

Hi Guys,

Can you please help me with the following Search in LIbre Calc.
I tried every option that I could think of but couldn’t get it to work.

The problem goes like this (just and example)
I have 2 predefined numbers:
B3=5
B4=10

And I have a sting of sorted (from smaller to higher) numbers, like this:
1,2,7,11,15,20

I am looking for a function to Search and give me a number from the string between the two predefined values.
Note: There is only one number that matches the criterion to be between the 2 numbers.
In this example it should give me 7

I tried using something like this:
IF(AND(D$19:D$29>B3,D$19:D$29<B4),1,0)

But I always get: N/A, Err. or something similar.

Glad if anyone can give me some pointers :slight_smile:
Thanks, Nikola

#2

Data>Text to columns … can split the string into 6 cell values.
Then =LOOKUP(B4;$A$1:$F$1) returns 7.

1 Like
#3

Alternative - kilometer length formula. :slight_smile:

#4

… or using LET() and a function from Lupp’s toolbox.
disask_128844_PickNumberFromSequenceString.ods (30.5 KB)

#5

…or simply:

=LOOKUP(B4;REGEX(D19;"\d+";;SEQUENCE(LEN(D19)))*1)
#6

Or use TEXTSPLIT appeared in 25.8:

=HLOOKUP(10;VALUE(TEXTSPLIT("1,2,7,11,15,20";","));1)
1 Like
#7

Fails with #N/A when using references: =HLOOKUP(B4;VALUE(TEXTSPLIT(A1;","));1)

#8

Works fine here. Are you sure that 10 in B4 is a number?

image

#9

why HLOOKUP instead LOOKUP?

#10

I was missing the call to VALUE.

#11

Doesn’t matter. Both are equivalent.

#12

yes…but HLOOKUP needs an additional Argument.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.