3

G'day, I'm working on a finance project using Google Sheets and I think I'm getting in a bit over my head.

I am trying to reformat a list of transactions by truncating the date (mm/dd/yyyy and a space) so that I can sort them alphabetically. I tried to do this quickly using an ARRAYFORMULA, but it did not seem to work as I wanted to. I'm using it in conjunction with the REPLACE formula:

=ARRAYFORMULA(REPLACE(A2, 1, 11, "")

I've made an spreadsheet here to exemplify the problem.

It doesn't throw an error, but it also doesn't generate a full array. I would be most grateful if someone could explain why this isn't working, or perhaps clarify the way ARRAYFORMULA function is supposed to work.

Thanks!

Hal
  • 29
  • 1
  • 3
  • We need a (X)`*`(Y) =1 construct instead of `AND` for a negative match combined with a positive match case as shown here: https://stackoverflow.com/a/43710932/10789707 vs here https://stackoverflow.com/a/54835596/10789707 for it to work with the arrayformula. – Lod Aug 12 '23 at 20:09

1 Answers1

4

Try this

=Arrayformula(TRIM(REGEXREPLACE(A2:A,"^\S+","")))

enter image description here

Rocky
  • 950
  • 1
  • 7
  • 12
  • Even though the spreadsheets functions are generally case-insensitive, the RE2 expressions are case-sensitive, correct? (particularly with that \S character) – Hal Jun 10 '20 at 02:43
  • Yes "\S" is case sensitive – Rocky Jun 10 '20 at 05:54