How to incorporate SUBSTITUTE function into complicated function in Google Sheets

by Todd Benrud   Last Updated February 17, 2017 12:03 PM

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.



Answers 1


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 inclued 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 a the an argument or function is changed.

Rubén
Rubén
February 17, 2017 09:27 AM

Related Questions


How to get the intersection of two sets

Updated April 07, 2015 03:01 AM