CODEWITHSUNDEEP
regexgoogle-sheets
GeorgeC
GeorgeC

Reputation: 1037

Regex find value between a space and period for Google Sheet

I have the following cell values:

LOT: 4 SP: 144731
LOT: 7 RP: 804923
LOT: 6 RP: 804923

I want to obtain:

SP
RP
RP

I have been trying the following where the values are in column N:

=ARRAYFORMULA(IF(REGEXMATCH(N2,"LOT: [1-9]+? :"),TRIM(REGEXREPLACE(N2,".*?(?:LOT: ([1-9]+?) :|$)","$1 ")),))

Getting the lot value using the following worked but I need the SP/RP etc value

=ARRAYFORMULA(IF(REGEXMATCH(N2,"LOT: .+? RP:"),TRIM(REGEXREPLACE(N2,".*?(?:LOT: (.+?) RP:|$)","$1 ")),))

Upvotes: 1

Views: 4301

Answers (3)

TheMaster
TheMaster

Reputation: 50799

There are a couple of ways you could formulate a REGEX expression. The common base idea is to find the "anchor"- the common factor.

There's already a strong accepted answer written by a expert. I'll just provide a couple of alternative solutions based on some assumptions based on the data provided by you:

Anchor P:

Assuming the extract will always contain a P. Provided everything else remains the same,the easiest and shortest one: 

=REGEXEXTRACT(N1,"\wP")

Anchor Space:

Assuming there's always a space before the required extract and it's the only space before the two non Digit: 

=REGEXEXTRACT(N1," (\D\D)")

Anchor ::

Two word characters followed by a : 

=REGEXEXTRACT(N1,"\b(\w\w):")

Upvotes: 1

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627469

You may use

=REGEXEXTRACT(N2, "LOT:\s+\S+\s+(\S+):")

Details

  • LOT: - a literal substring LOT:
  • \s+ - 1+ whitespaces
  • \S+ - 1+ non-whitespace chars
  • \s+ - 1+ whitespaces
  • (\S+) - Group 1 (this part will be returned by the REGEXEXTRACT function): 1+ non-whitespace chars
  • : - a colon

See the regex demo.

Upvotes: 2

Max Makhrov
Max Makhrov

Reputation: 18727

for text in A1:

=REGEXEXTRACT(A1,"\d (.*):")

ArrayFormula for text in A1:A3:

=ArrayFormula(REGEXEXTRACT(A1:A3,"\d (.*):"))

Upvotes: 1

Related Questions