Regex substring formula

Hi! I want to create a formula that returns the partial match of a string given a regex like this ^[A-Z][0-9] which will find and return KA7030 in the formula column. Is that possible?

This is an example dataset

KA7030ROJO1
KA7030ROJO2
KA7030ROJO3
KA7030ROJO4
KA7050PIEL4

Also consider alternatives. For your example dataset would be enough a simple

=LEFT(A1;6)

or standard Data-Text to columns

1 Like

Definitely the best for the given dataset

For this specific task, =MID(A1;SEARCH("^[A-Z]*[0-9]*";A1);SEARCH("[^0-9]";A1;SEARCH("[0-9]";A1))-1) will do. But @erAck has introduced a new REGEX function (tdf#113977) for upcoming LO 6.2.

Actually the regex should be ^[A-Z]+[0-9]+ (i.e. at least one of each character set) to not match empty sub expressions. Or probably even narrowed to ^[A-Z]{2}[0-9]{4} in this case.

Yes; just used OP’s regex verbatim - but that would make my formula unusable with different input patterns. Thanks @erAck!