[FIXED] How to Split a String Up into Individual Characters

Issue

Using array formula, how to divide each character into a cell.

Input   Output                                              
cat     c   a   t                                       
dog     d   o   g                                       
horse   h   o   r   s   e                               
tiger   t   i   g   e   r                               

Solution

Here's another possible solution.

=ArrayFormula(
 Iferror(split(regexreplace(A2:A,
               "(.)","$1❄️"),"❄️"),""))

This formula uses REGEXREPLACE to insert a placeholder character after each character, and it SPLITs by it.

ARRAYFORMULA - IFERROR - SPLIT - REGEXREPLACE

Answered By - ztiaa

Answer Checked By - Gilberto Lyons (PHPFixing Admin)

Read More

[FIXED] How to reference to a previous row within a column with Arrayformula in Google Sheets

Issue

i'm working on a schedule tool, but can't figure something out. I'd like to use the arrayformula function in Google Sheets. It should sum up two values from the previous row.

At the moment I'm using this, but i'd like to convert it into an arrayformula. I've tried several things, but end up with the 'Circular dependency detected' message.

=iferror(IF(REGEXMATCH(C5;"DAY");E5;index(G:G;ROW(G5)-1;1)+H6);index(G:G;ROW(G5)-1;1)+H6)

Here's the doc:

https://docs.google.com/spreadsheets/d/15jXayEN_vjK3nMRjLjaiLspT_rh_rBD_WX_LAZETRSU/

Solution

try:

=INDEX(IF(IFERROR(REGEXMATCH(C3:C; "(?i)DAY")); E3:E; SCAN(E3; H3:H; LAMBDA(x; y; x+y))))

Answered By - player0

Answer Checked By - Willingham (PHPFixing Volunteer)

Read More

[FIXED] How can I combine a Google Sheets query formula with arrayFormula formulas?

Issue

Code from @doubleunary's answer to: How can I improve the performance of this Google Sheets custom function?

My 'query' sheet has a query formula to filter an 'INPUT' sheet. The query is:

=query(INPUT!A1:K, "select A, max(G), H where I = 'Pilot' group by A, H pivot D", 1)

The 'INPUT' sheet values are:

Instance Id	Group name	Group Id	Field name	Field Id	Type	Value	File Id	Role	Concatenated Id	VLookup value
Instance1A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 1	09456c1a-abb4-4e81-94bd-7ce4c88afffc	CURRENCY	100	Pilot_File	Pilot	09456c1a-abb4-4e81-94bd-7ce4c88afffcInstance1A	100
Instance1A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 2	474f6395-83a7-4c2b-aa5a-ceb00e200f8e	CURRENCY	200	Pilot_File	Pilot	474f6395-83a7-4c2b-aa5a-ceb00e200f8eInstance1A	200
Instance1A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 3	ac64e001-fe85-400a-92e4-69cebf1c260d	CURRENCY	300	Pilot_File	Pilot	ac64e001-fe85-400a-92e4-69cebf1c260dInstance1A	300
Instance1B	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 1	09456c1a-abb4-4e81-94bd-7ce4c88afffc	CURRENCY	110	Pilot_File	Pilot	09456c1a-abb4-4e81-94bd-7ce4c88afffcInstance1B	110
Instance1B	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 2	474f6395-83a7-4c2b-aa5a-ceb00e200f8e	CURRENCY	220	Pilot_File	Pilot	474f6395-83a7-4c2b-aa5a-ceb00e200f8eInstance1B	220
Instance1B	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 3	ac64e001-fe85-400a-92e4-69cebf1c260d	CURRENCY	330	Pilot_File	Pilot	ac64e001-fe85-400a-92e4-69cebf1c260dInstance1B	330
Instance2A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 1	09456c1a-abb4-4e81-94bd-7ce4c88afffc	CURRENCY	1000	Co-PIlot_File	Co-Pilot	09456c1a-abb4-4e81-94bd-7ce4c88afffcInstance2A	1000
Instance2A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 2	474f6395-83a7-4c2b-aa5a-ceb00e200f8e	CURRENCY	2000	Co-PIlot_File	Co-Pilot	474f6395-83a7-4c2b-aa5a-ceb00e200f8eInstance2A	2000
Instance2A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 3	ac64e001-fe85-400a-92e4-69cebf1c260d	CURRENCY	3000	Co-PIlot_File	Co-Pilot	ac64e001-fe85-400a-92e4-69cebf1c260dInstance2A	3000

Which gives the following result on the 'query' sheet:

Instance Id	Field 1	Field 2	Field 3	File Id
Instance1A	100	200	300	Pilot_File
Instance1B	110	220	330	Pilot_File

Another 'RESULTS_PILOT' sheet uses two arrayFormula formulas to format the 'query' sheet values into a table with values placed in specific columns according to a 'TABLE_CONFIG' sheet. The formulas are:

cell A1:

=arrayformula( 
  { 
    "Instance Id", 
    iferror( 
      vlookup( 
        sequence(1, max(TABLE_CONFIG!C2:C4) - 1, 2), 
        { TABLE_CONFIG!C2:C, TABLE_CONFIG!B2:B }, 
        2, false 
      ) 
    ), 
    "File Id" 
  } 
)

cell A2:

=arrayformula( 
  iferror( 
    hlookup( 
      A1:I1, 
      query!A1:E, 
      sequence(counta(query!A2:A), 1, 2), 
      false 
    ) 
  ) 
)

The 'TABLE_CONFIG' sheet is:

Field Id	Description	Desired table field column	Group Id
09456c1a-abb4-4e81-94bd-7ce4c88afffc	Field 1	1	91c7db0a-c52a-407d-869a-af8ba8bf8ba7
474f6395-83a7-4c2b-aa5a-ceb00e200f8e	Field 2	3	91c7db0a-c52a-407d-869a-af8ba8bf8ba7
ac64e001-fe85-400a-92e4-69cebf1c260d	Field 3	5	91c7db0a-c52a-407d-869a-af8ba8bf8ba7

So the table on the 'RESULTS_PILOT' sheet appears like:

Instance Id	Field 1		Field 2		Field 3	File Id
Instance1A	100		200		300	Pilot_File
Instance1B	110		220		330	Pilot_File

Is there a way to combine the query with the arrayFormula formulas on the 'RESULTS_PILOT' sheet, so that the 'query' sheet is not required?

Solution

try:

=INDEX(LAMBDA(x, y, {y; IFERROR(HLOOKUP(y, x, 
 SEQUENCE(COUNTA(QUERY(x, "offset 1", )), 1, 2), ))}) 
 (QUERY(INPUT!A1:K, "select A, max(G), H where I = 'Pilot' group by A, H pivot D", 1), 
 {"Instance Id", IFERROR(VLOOKUP(SEQUENCE(1, MAX(TABLE_CONFIG!C2:C4)-1, 2), 
 {TABLE_CONFIG!C2:C, TABLE_CONFIG!B2:B }, 2, )), "File Id"}))

Answered By - player0

Answer Checked By - Willingham (PHPFixing Volunteer)

Read More

[FIXED] How to use arrayformula with GCD?

Issue

I'm trying to use this formula to determine if Column A is a multiple of 5, but the arrayformula isn't expanding.

=ARRAYFORMULA(IF(GCD(5,A1:A)=5,TRUE,FALSE))

GCD doesn't seem to support arrayformula. How to make it work with ARRAYFORMULA?

Column C is the result of using autofill.

A	B[Arrayformula]	C[DragFill]
1	FALSE	FALSE
2		FALSE
3		FALSE
4		FALSE
5		TRUE
6		FALSE
7		FALSE
8		FALSE
9		FALSE
10		TRUE
62		FALSE
34		FALSE
22		FALSE
75		TRUE
34		FALSE
46		FALSE
30		TRUE
21		FALSE
92		FALSE
56		FALSE

Solution

try:

=BYROW(A1:A20, LAMBDA(x, GCD(5, x)=5))

or just:

=INDEX(MOD(A1:A20, 5)=0)

Answered By - player0

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)

Read More

[FIXED] How to eliminate incomplete strings?

Issue

I have a list of keywords, but the program I am using truncates the list at 160 characters, so I am unable to fully see the last keyword string.

I want to use Google Sheets to remove the incomplete parts of the keyword list. This would mean removing the last element altogether, as the picture illustrates.

I have tried using the trimming function but without much success. How can incomplete strings (any string that contains three dots at the end) be removed from the list?

Solution

try:

=ARRAYFORMULA(IFNA(REGEXEXTRACT(A2:A, "(.*), ")))

Answered By - player0

Answer Checked By - Robin (PHPFixing Admin)

Read More