Help with Google Sheets formula REGEX_EXTRACT pattern

Questions & Answers
,
1

Hello Makers, Regex is giving me hard time (once again)…

I’m using the following formula:

IF(HostsList,REGEX_EXTRACT(
HostsList&“”,
“(?:” & SUBSTITUTE(DATETIME_FORMAT(DATEADD(Date,-1,‘day’), “DD.MM”), “.”, “\.”) & " )([^,]*)“),”-")

Date = 11/4/2024
HostsList =

  1. 07.04 Peshtan
  2. 08.04 Mani Damani
  3. 09.04 Sotir Kristiani
  4. 10.04 Edmond Duli
  5. 11.04 Alvero
  6. 12.04 Kodra
  7. 13.04 Vila 60

The desired output is “Edmond Duli”, but I’m unable to remove the string after the actual match:

Edmond Duli
5. 11.04 Alvero
6. 12.04 Kodra
7. 13.04 Vila 60

Thanks for your input!

2

Hello Zbulo, does this have anything to do with Make?

If it has to do with a formula within Google Sheets, try the Google Sheets forum.

2 Likes
3

He @samliew, it’s actually an Airtable formula question, I only tagged it Airtable, but missed including an Airtable remark in the title/text.

That said, here’s the solution I applied. I couldnt combine both REGEX_EXTRACT in one and ended up splitting them up.

REGEX_EXTRACT(
IF(
HostsList,
REGEX_EXTRACT(
HostsList&“”,
“(?:” & SUBSTITUTE(DATETIME_FORMAT(DATEADD(Date,-2,‘day’), “DD.MM”), “.”, “\.”) & " )([^,])"
),
“-”
) & “”,
"^(.?)(?:\r\n|\r|\n|$)"
))