Ask Your Question
0

Regex substring formula

asked 2018-11-30 00:14:44 +0200

razor7 gravatar image

updated 2018-11-30 00:15:31 +0200

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
edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2018-11-30 07:26:46 +0200

JohnSUN gravatar image

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

=LEFT(A1;6)

or standard Data-Text to columns

Extract part of string

edit flag offensive delete link more

Comments

Definitely the best for the given dataset

Mike Kaganski gravatar imageMike Kaganski ( 2018-11-30 07:35:12 +0200 )edit
0

answered 2018-11-30 05:56:42 +0200

updated 2018-11-30 07:11:17 +0200

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.

edit flag offensive delete link more

Comments

1

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.

erAck gravatar imageerAck ( 2018-11-30 15:22:03 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2018-11-30 15:31:47 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-11-30 00:14:44 +0200

Seen: 694 times

Last updated: Nov 30 '18