I’m trying to select a string in Libre Office Calc, with this function and is working
=INDEX(AI2:AL2,MATCH("[A-Z0-9]+" ,AI2:AL2,0))
However, when I put a sequence of string (sentence), it gives me an #N/A. when I checked the problem the function rejected the spaces between the words.
Is there is a way to deal with space and lets the MATCH function deal with a full string?
Thank you
Please attach an example file showing what you have and how MATCH() should work with it.
Or at least:
Sufficiently many and clear examples of cell contents you want to be
-a- accepted
-b- rejected
by MATCH() .
The examples should clearly distinguish “acceptable” and “rejectable” and not leave a “probably”.
The INDEX() part shouldn’t be relevant so far.
You obviously work with the option to use regular expressions with eligible functions.
In addition you chose the option to the effect tjhat search criteria must apply to whole cells (which is clearly recommendable).
Therefore the Regex [A-Z0-9]+ is equivalent to ^[A-Z0-9]+$ and cannot find a cell containing a space. You need to refine the RegEx to better meet your needs (which I don’t know).
(BTW: Please also consider Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet?)
“deal with space” and “deal with a full string” is a completely ambiguous requirement. This ^.*[A-Z0-9]+.*$ deals with a full string by picking an ASCII-alnum sequence anywhere, this ^[A-Z0-9]+.*$ deals with a full string by picking an ASCII-alnum sequence at the start, this ^.*[A-Z0-9]+$ deals with a full string by picking an ASCII-alnum sequence at the end, this ^.*[A-Z0-9 ]+.*$ deals with a full string by picking an ASCII-alnum+space sequence somewhere.