Google Sheets – How to Incorporate SUBSTITUTE Function into Complex Functions

google sheetsregexextractworksheet-function

I have the following function and it works perfectly, with one exception. Some of the values returned have characters in them that I want to remove. 

=ARRAYFORMULA(IFERROR(REGEXEXTRACT("|"&REGEXREPLACE(F2:F,"\n","|"),"^"&REPT("\|[^|]*",COLUMN(OFFSET(C1,,,1,5))-1)&"\|([^|]*)")))

The string in F2 is: OER - World History - Day 1 | 9/1/2016 | @ 8:00:00 AM | Location is 000-DO | in Jamacha Conference Room | ID#100008

The above function currently returns @ 8:00:00 AM

I'd like the above function to return 8:00:00 AM

I've tried using the SUBSTITUTE function on the above function, but I can't figure out 'where' or 'how' to properly use it. If I use it on a simple string, it works perfectly. 

=SUBSTITUTE(?????, "@ ", "")

Any help is greatly appreciated.

Best Answer

Short answer

=ARRAYFORMULA(
  SUBSTITUTE(
    IFERROR(
     REGEXEXTRACT(
      "|"&REGEXREPLACE(F2:F,"\n","|"),
      "^"&REPT("\|[^|]*",
      COLUMN(OFFSET(C1,,,1,5))-1)&"\|([^|]*)")
    ),
  "@","")
)

Explanation

The argument of the ARRAYFORMULA function, was included as the first argument of SUBSTITUTE and this was this was included as argument of the ARRAYFORMULA.

NOTE:

Breaklines and indents were included for readability. Google Sheets is able to handle this, but only will keep the breaklines and indents when an argument or function is changed. I recently added a more extended explanation on my answer to Pretty print google sheet formula?

Related Topic