Google-sheets – How to incorporate SUBSTITUTE function into complicated function in Google Sheets

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.


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



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.


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?