How to use multiple delimiters with Split() like google sheet?

[vei123]

I am trying to use split in appsheet but only limited to 1 delimiter. Tried to use multiple delimiter but not working.

Is there a way I can use these delimiter like on google sheet and it will only give me number list?

In google sheet: 

I can use, SPLIT(lower("(99)QUEJSJLS5555777 ","qwertyuiopasdfghjklzxcvbnm`-=[]\;',./!@#$%^&*()")

Example: (99)QUEJSJLS5555777 returns only (99,5555777)

Tried to use in appsheet but not working. It only lower case the letters.

[Aurelien · Accepted solution]

---

@vei123 wrote:

de

---

Hi @vei123 

You can't.

however, you can try creating a custom function with Apps Script, and use a bot to convert your chain string, and writing down the output of the function.

For reference:

Bots: The Essentials - AppSheet Help

Use return values from Apps Script tasks - AppSheet Help

[vei123]

Thank you. Now I know it is not yet possible. Hoping this will be integrated someday.

[Aurelien]

Hopefully ! 🙂

[MultiTech]

You could try.....

substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(
substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(
substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(

	TRIM("(99)QUEJSJLS5555777 "),
	
" , ", "_"), ", ", "_"), " ", "_"), "-", "_"), left("' ", 1), ""), left('" ', 1), ""), "/", ""), "\", ""), ".", "_"), ",", ""), ":", ""), ";", ""), "[", ""), "]", ""), "(", ""), ")", ""), "{", ""), "}", ""), "!", ""), "@", ""), "#", ""), "$", ""), "%", ""), "^", ""), "&", ""), "*", ""), "+", ""), "?", ""), "|", ""), "<", ""), ">", ""), "`", ""), "~", ""), right(' =', 1), ""), "_____", "_"), "____", "_"), "___", "_"), "__", "_"),

"a", ""), "b", ""), "c", ""), "d", ""), "e", ""), "f", ""), "g", ""), "h", ""), "i", ""), "j", ""), "k", ""), "l", ""), "m", ""), "n", ""), "o", ""), "p", ""), "q", ""), "r", ""), "s", ""), "t", ""), "u", ""), "v", ""), "w", ""), "x", ""), "y", ""), "z", ""),

"A", ""), "B", ""), "C", ""), "D", ""), "E", ""), "F", ""), "G", ""), "H", ""), "I", ""), "J", ""), "K", ""), "L", ""), "M", ""), "N", ""), "O", ""), "P", ""), "Q", ""), "R", ""), "S", ""), "T", ""), "U", ""), "V", ""), "W", ""), "X", ""), "Y", ""), "Z", "")

Messy.... but it removes all the special characters and letters from whatever you put inside the TRIM().... 🤔 

[SheetsFairy]

Google Apps Script solution:

function splitTextOnMultipleDelimiters(string, delimiters) {

var array = [string];

for (i=0; i<delimiters.length; i++) {

var array = array.map(a => a.split(delimiters[i])).flat()

}

return array

} function testSplit() {

var string = 'First: Split this paragraph on colons followed by spaces. Next: Split again on periods followed by spaces. Thank you.'

var delimiters = [': ','. ']

console.log(splitTextOnMultipleDelimiters(string, delimiters))

}

[ 'First', 'Split this paragraph on colons followed by spaces', 'Next', 'Split again on periods followed by spaces', 'Thank you.' ]