CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formula
pinklino
pinklino

Reputation: 11

Google Sheets REGEXEXTRACT splits into two cells

I have cell B10 with text that contains slashes and I want to extract the text around those slashes.

Example: Text in B10 is PV AA/BB 1250/2500/13 and I want to extract 1250/2500 in cell O10.

I tried to do =REGEXEXTRACT(B10, "([1-9][0-9][0-9][0-9])\/([1-9][0-9][0-9][0-9])") but for some reason, this splits the result into two cells (O10 and O11), without the /. Similar happens when I try to extract the AA/BB with ([a-zA-Z]+)\/([a-zA-Z]+) - it returns AA and BB in two separate cells.

What is happening here? How do I extract the string into one cell only, with the slash?

Upvotes: 1

Views: 877

Answers (3)

The fourth bird
The fourth bird

Reputation: 163362

You can 2 capture groups, but for a match you don't need any groups, and the / also does not have to be escaped.

=REGEXEXTRACT(B10, "[1-9]\d{3}/[1-9]\d{3}")

enter image description here

Or use REGEXREPLACE with the 2 capture groups and the / in the replacement and matching all chars before and after it so that they won't be present after the replacement.

=REGEXREPLACE(B10, ".*([1-9]\d{3})/([1-9]\d{3}).*", "$1/$2")

enter image description here

Upvotes: 0

player0
player0

Reputation: 1

try:

=REGEXEXTRACT(B10; "\d{4}.\d{4}")

enter image description here

or:

=REGEXEXTRACT(B10; " (\d+.\d+)")

Upvotes: 0

Shah
Shah

Reputation: 51

you have extra brackets "()" in your regular expression it should be,

=REGEXEXTRACT(B10, "([1-9][0-9][0-9][0-9]\/[1-9][0-9][0-9][0-9])")

the brackets ")(" before and after / is removed to get the desired result

Upvotes: 1

Related Questions