GS Query to split a column (Last, First) into 2 columns

user_16498491 0
Viewed 80

I have a 'Raw Invoice' tab which is an excel file copy/pasted directly over. I'm then trying to format the data in the 'Invoices' tab in that column order using a query. I need to be able to break out the Student Name into two separate columns, hopefully within the query itself. Preferably it would then change it so column C is Last Name and column D is First name and the rest of columns shift over one.

I don't know if there's a way to perform a SPLIT function within the query. Right now I'm using a clunky method by doing a VLOOKUP on the student ID to get the names from another tab (not included in the Sample GS cuz it's an importrange from a work file), but it then creates two separate queries. Ideally I can somehow split column C within one query, but am getting lost by nesting queries and arrays together. I might be able to use REGEXEXTRACT, but again get lost in where to put it in the query or whether that's overkill.

QUERY('Raw Invoice'!$A:$I,"Select F,B,A, 'Bobs Diner',D,G,I where C is not null label 'Bobs Diner' 'Company' Format F 'M/DD/YYYY' ",1)

Link to sheet.

google-sheets-formula google-sheets
2 Answers

Wat I suggest is to implement in Row Invoice

=arrayformula(split(A3:A,","))

and then

=QUERY('Raw Invoice'!$A:$K,"Select F,B,J,K, 'Bobs Diner',D,G,I where C is not null label 'Bobs Diner' 'Company' Format F 'M/DD/YYYY' ",1)
user_150193800

split in query arguments is not possible but you can do:

=ARRAYFORMULA(QUERY({IFERROR(SPLIT('Raw Invoice'!A:A, ", ")), 'Raw Invoice'!B:I}, 
 "select Col7,Col3,Col1,Col2,'Bobs Diner',Col5,Col8,Col10
  where Col3 is not null
  label 'Bobs Diner' 'Company'
  format Col7 'M/DD/YYYY'", 1))

enter image description here

user_56326290
Related