Disclaimer: This post talks about entering actual comma separated values (CSV) data into an input box / cell and NOT about importing a
.csv(excel) file onto the tool.
I want to take a list of comma separated values and have them distributed over a range of cells using the comma (
Or, might’ve even tried combining ARRAYFORMULA, VLOOKUP, TRIM, QUERY & of course, sprinkled the occasional SPLIT & TRANSPOSE to get what you need
Side note: This is no joke; you could find a solution that uses all the aforementioned functions, here.
While at dashdash we’re still working on bringing you as much flexibility as you may need to have the best experience, here’s how you can achieve this task from our dashboard, today.
- Identify all the comma positions in a given set of values using the FIND function and log them on a separate view for processing
– Ex: In the sequence
You,Will,Always,Be,Awesomethe comma appears at the 4th, 9th, 16th & 19th positions
- Once you have these, it’s then only a matter of designing a combination to execute LEFT & RIGHT functions that’ll help capture either the beginning or the end of the sequence (I do hear my ‘spreadsheet nerd’ gene spinning it’s wheels right about now )
- Start with creating 2 views, then add them to a single group
– I’d name the first view as ‘CSV Upload’ and the second one as ‘CSV Processor’
- Specify an input cell in the first view (i.e. ‘CSV Upload’); for the purposes of this post, assume that to be
- Identify a cell below which you’d like the comma separated values to show-up once they’re processed & use it as a header - I’ve chosen
B5in the same first view and named it as ‘Data’
– Feel free to play around with the logic on when you’d like to have the data processed i.e. after someone presses a button, or instantly as when you see someone fill in the data etc.
- On the second view (i.e. ‘CSV Processor’), choose two columns where the first would log all the ‘Comma Position’ and the other would be to log the actual value from the comma separated sequence (keep the same name on the 2nd column’s header as that of the output column from our first view - I’ve kept it as ‘Data’)
- On the ‘CSV Processor’ view, add the following formulas:
A2: =IF(AND(A1<>"",SUM(A1)=0),IFERROR(FIND(",",'CSV Upload'!$B$1,1),""),IF(AND(A1<>"",SUM(A1)<>0),IFERROR(FIND(",",'CSV Upload'!$B$1,A1),""),""))
B2: =IF(AND(SUM(A1)=0,A2<>""),IFERROR(RIGHT(LEFT('CSV Upload'!$B$1,A2-1),A2-SUM(A1)-1),""),IF(AND(SUM(A1)<>0,SUM(A1)<>"",A2<>""),IFERROR(RIGHT(LEFT('CSV Upload'!$B$1,A2-1),A2-SUM(A1)-1),""),IF(AND(A1<>"",A2=""),IFERROR(RIGHT('CSV Upload'!$B$1,LEN('CSV Upload'!$B$1)-SUM(A1)),""),"")))
- You’re free to drag the formulas all the way down to the 100th or 1000th rows, depending on the limit you’d like to set on the number of values you’d want to have processed at any given point in time
- With the FIND function (the one being used to locate ‘Comma Position’), what I’m doing is if it’s the first occurrence, I’m letting it be handled by the default 1st position to begin it’s search; however, the subsequent FIND makes use of the prior function’s output as it’s starting point
- The combination of RIGHT and LEFT functions too make use of current and previous comma positions as markers to isolate the substring and return only the one that’s relevant in the order of the sequence
Here’s a published app where you can experiment by entering about 10-15 comma separated values and while you are free to add upto a 100 values (intentionally restricted), you may experience a delay with actually being able to see the output on the same view.
I bet there are a couple dozen other ways to get what you need with these methods so feel free to post your alternates or shoot me a DM in case you simply want to brainstorm other ideas
In case you just landed on this article and don’t yet have an account with us, do sign up here to get started!
Sourabh | No-Code Builder