1

Scenario

Consider the following query:

=QUERY(A15:E861, "select B, sum(D) group by B")

This works for rows with col B's that have the SAME description eg.

1 | abc | n | 10
2 | abc | n | 10
3 | cba | n | 10

Will result in

abc | 20
cba | 10

Question

But let's say I have

1 | abc-#ref123 some description    | n | 10
2 | abc-#ref456 another description | n | 10
3 | cba-#ref889 last description    | n | 10

And I want to ignore the "$refxxx description". How do I go about doing this?

  • regex it out before passing it to query – TheMaster Jul 21 '18 at 16:41
1

F1:
=ARRAYFORMULA(SUBSTITUTE(B15:B861; B15:B861; LEFT(B15:B861; 3)))
and then:
=QUERY(A15:H861; "select F, sum(D) group by F")

  • In practice the text I wanted was more in the middle so I used MID instead of LEFT but this definitely got me to my solution thanks. – Jacksonkr Jul 22 '18 at 0:07

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.