How can I reference a cell in a custom function while setting up data validation in Google spreadsheets?

I made the following Google script to check whether a string is less than some length:

function isLengthLessThan(length, text) {   return (text.length < length) }; 

How can I set up data validation using this custom formula to check the length of cells? What can I put where the question mark is so that it will represent the value of the cell?

custom formula data validation screen

How can I reference a cell in a custom function while setting up data validation in Google spreadsheets?

I made the following Google script to check whether a string is less than some length:

function isLengthLessThan(length, text) {   return (text.length < length) }; 

How can I set up data validation using this custom formula to check the length of cells? What can I put where the question mark is so that it will represent the value of the cell?

custom formula data validation screen

How to use the SPLIT function in Google Spreadsheets with different delimiters and with some delimiters with multiple characters?

My question is similar to this one: SPLIT function in Google Spreadsheets with OR statement

but has the following difference:

I want to use the delimiter “apple” and the delimiter “.” within the SPLIT function.

Since the delimiter apple has multiple characters, @parkydr answer here doesn’t work.

Example:

On this input on A2:

I ate an apple on the bus home. Going 

I want it to split like this:

I ate an / on the bus home / Going 

I tried this:

=SPLIT(A2,"apple.",TRUE)  I / t   /   n   / on / th   / bus / hom / Going 

and this

=SPLIT(A2,"'apple''.'",TRUE)  I / t   /   n   / on / th   / bus / hom / Going 

and this

=SPLIT(A2,"apple.",false)  I ate an apple on the bus home. Going 

and this

=SPLIT(A2,"apple"".",false) #ERROR Formula parse error. 

and this

=SPLIT(A2,"'apple''.'",false) I ate an apple on the bus home. Going 

How to make return the next split?

I ate an / on the bus home / Going 

Thanks a lot for your help!

How can I set the increment value for Google Spreadsheets to auto-increment?

I have a series of cells referencing other cells:

 | Row |  a  |  b  | col B shows |  |-------------------------------|  |  1  |  1  | =a2 |      2      |  |  2  |  2  | =a4 |      4      |  |  3  |  3  | =a6 |      6      | 

If I select cells b1,b2 & b3, then drag the autofill corner to fill in b4, b5, & b6, the fill pattern follows the increment of the “source” cells, i.e, “+2”, but the autofill seems to start with a value based on the number of cells I’ve selected. It looks like the app takes the value of the first cell in the series (“=a2”, in this case) and increments the autofill value by the number of cells selected in the series (3, here), So rather than auto-filling cell b4 with the next value in the progression (“=a8”), b4 is auto-filled with “=a5”.

 | Row |  a   |  b   | col B shows |  |---------------------------------|  |  1  |  1   | =a2  |      2      |  |  2  |  2   | =a4  |      4      |  |  3  |  3   | =a6  |      6      |  |  4  |  4   | =a5  |      5      |  |  5  |  5   | =a7  |      7      |  |  6  |  6   | =a9  |      9      |  |  7  |  7   | =a8  |      8      |  |  8  |  8   | =a10 |      10     |  |  9  |  9   | =a12 |      12     |  |  10 |  10  | =a11 |      11     | 

If I select 4 cells before dragging to auto-fill, it adds “4” to “a2” and places “=a6” where I would expect it to place “=a10”. Select 5 cells, I get “=a7” where I’d expect “=a12”.

It looks to me like auto-fill is placing the number of values in the series (the series length) above the series increment values. I can see where this would be “by design”, but I want to auto-fill my progression by a set increment, not a set series length.

Hope y’all can help! Please forgive me if I’ve butchered mathematical terminology

Hide irrelevant decimal fractional part of numbers in spreadsheets

Is there any way to format numbers in 123/Excel-compatible spreadsheet applications, Google Spreadsheets in particular, in a way that hides the fractional part and the preceding decimal separator (i.e. dot or comma depending on locale) if it is zero (i.e. the number is an integer)?

123

  • 0.0 &rightarrow; 123.0
  • 0.# &rightarrow; 123. this is the closest I get
  • 0.? &rightarrow; 123.␠
  • 0_.0 &rightarrow; 123 0
  • 0_.# &rightarrow; 123 
  • 0_.? &rightarrow; 123 ␠

1.23

  • 0.0% &rightarrow; 123.0%
  • 0.#% &rightarrow; 123.%
  • 0.?% &rightarrow; 123. %
  • 0_.0% &rightarrow; 123 0%
  • 0_.#% &rightarrow; 123 %
  • 0_.?% &rightarrow; 123  %

I know that one can format positive, negative and zero-value numbers separately with semicolons: +0.0%;−0.0%;±0_?%

Sort a Google Spreadsheets so it returns ‘alternating’ values in a column

I have a list of hundreds of students that contains the following data. The only two values that will appear in the gender column (col 3) are male or female:

Current List

    fName | lName | Gender | id# ----------------------------------------     Mike  | Jones | male   | 123456     Todd  | Smith | male   | 987654     Kim   | Holly | female | 753951 

I want to sort the sheet so it returns alternating values (male/female) in the gender column.

Desired List

        fName | lName | Gender | id#     ----------------------------------------         Mike  | Jones | male   | 123456         Kim   | Holly | female | 753951         Todd  | Smith | male   | 987654 

Sort Google Spreadsheets so it returns ‘alternating’ values in a column

How can I sort a random list in Google SHeets so it returns alternating values by Room? Or even dynamic based on unique Rooms, In case you add a new Room.

The current list looks like this:

fName | lName | Room   | ID ------------------------------- Sara  | Don   | 104    | 524134 Mark  | Don   | 104    | 323124 Rose  | Jones | 101    | 145456 Mike  | Jones | 101    | 123456 Todd  | Smith | 103    | 321313 Kim   | Holly | 102    | 753951 Paul  | Holly | 102    | 745951 Dave  | Smith | 103    | 355353 

The desired list would look like this:

fName | lName | Room   | ID ------------------------------- Mike  | Jones | 101    | 123456 Kim   | Holly | 102    | 753951 Todd  | Smith | 103    | 321313 Mark  | Don   | 104    | 323124 Rose  | Jones | 101    | 145456 Pau   | Holly | 102    | 745951 Dave  | Smith | 103    | 355353 Sara  | Don   | 104    | 524134 

Thanks, Ed

How to remove duplicate rows based on a column (leaving first one out) in Google Spreadsheets

I am trying to remove duplicates from my Google spreadsheet file.

Here is an initial table example

     A       B   C   D HelloWorld1  2   6  41 HelloWorld1  4   3   5 HelloWorld2  6   46  6 HelloWorld2  2   4   4 HelloWorld2  5   6   2 HelloWorld3  9   3   5 

It should remove all duplicates but leave the fist entry as follows:

     A       B   C   D HelloWorld1  2   6  41 HelloWorld2  6   46  6 HelloWorld3  9   3   5 

I have tried different formulas and techniques, however I haven’t got them working. Sorting is not suitable, because I need to leave the same order of rows.

I have tried the following formula for removing, based on the first column. However, I got An array value could not be found

 =ArrayFormula(vlookup(UNIQUE(ARRAY_CONSTRAIN(A:A,COUNT(A:A),1)),A:D,column(A:D),1)) 

which would be he correct formula? It would be great if this could be done in a single formula or query.