Google-sheets – Query Function – remove parts of entered term and carry out formula

formulasgoogle sheetsgoogle-sheets-querysearchworksheet-function

I'm sorry if the title is confusing.
In the List to Search tab, I've got a bunch of orders. What I do is select the dishes in a single cell in this manner:

enter image description here

And I want to just dump them into the column A, dishes search section in Search for Dish tab. And the query should grab the relevant dishes from the Inventory tab, omitting the numbers before the dish name, i.e. "1_" or "1___". Basically just omit that part and only get the dish name without me having to do any extra work.

The numbers before the dishes would be in either of these 2 patterns mostly, "1_" or "1___".

If this detail helps, all dishes have an index number in this format D00 - or D000 -

This is the basic routine for my work, the copying of dishes from a single cell, so its important that I be able to just grab only the dish name with index number.

This is my spreadsheet https://docs.google.com/spreadsheets/d/1rlATwRPTkofEg0rB5-GSwwBpQDa7ZOoeNvhs0xPOsKA/edit#gid=719800634

I've tried INDIRECT & TRIM in the query formula like this: "where A='"&INDIRECT(A3)&", but they didn't work. Maybe the way I entered them were wrong.

Best Answer

You mentioned

...select the dishes in a single cell in this manner: ...just dump them into the column A, dishes search section in Search for Dish tab ...query should grab the relevant dishes from the Inventory tab, omitting the numbers before the dish name

Place the following in cell B3 in your NEW Search for Dish tab.

=IFERROR(TRANSPOSE(
           SPLIT(REGEXREPLACE($A$3, ".*?(?:(D\d+\s- \w+ \w+))","$1"),CHAR(10))), 
     "Paste the order in cell A3")

enter image description here

The above formula will do the work for you.
(Please adjust ranges to your needs. You can find a demo in your sheet.)

How to copy/paste for the formula to work.
Please do NOT double click the cell to copy the dishes.
Just single click on a cell, copy and paste it on cell A3 in your NEW Search for Dish tab.

Related Topic