1

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.

  • It's not a good idea to have complicated formulas. They make spreadsheets hard to be reviewed and hard to be maintained. Besides this, without sample input data it´s hard to be certain that solution will work and by the other side not all functions are able to work with / on array formulas. – Rubén Feb 17 '17 at 4:21
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 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?

| improve this answer | |
  • Ruben, thank you for the solution, the explanation, and for sharing the code in a manner that makes it easier to understand. You are the best! – Mr. B Feb 17 '17 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.