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

AppSheet AppSheet Q&A
1

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.

2

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

2 Likes
3

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

2 Likes
4

Hopefully ! :slightly_smiling_face:

1 Like
5

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()… :thinking:

4 Likes
6

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.’ ]