Exporting a comma separated set of numbers in a cell to an indepedent set of columns
Hi All,
I have something like this in a column QCEVCODE:
1,3,2,5,1
for let's say 1000 rows, each one of them is the amount of issues for a type of dataset. I need to be able to export this to independent columns (or any other way to happen to come across with) so i can build a diagram, graphic showing percentage of certain type of issues in i.e Q1 2021, Q2 2021, etc
i want to automatically feed a dashboard item, meaning i know i can export to excel and do it from there but i want to inform my crew LIVE,
Is this possible? I guess yes!
Thanks in advance!!!
Alberto
Best Answers
-
Will each number only be one digit? If so, we can use the LEFT Function, the RIGHT Function, and the MID Function (depending on what value we're bringing back) to extract the number.
For example, for the first number on the left, you can use:
=LEFT(QCEVCODE@row, 1)
Then for the second number in your second column:
=MID(QCEVCODE@row, 4, 1)
and so on. Then you can make it a column formula. Does that make sense? Let me know if it would be helpful to see a screen capture.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Ah, yes, thank you for clarifying. In this case, entering the values into separate columns makes sense and will be the best solution for now. I'm glad you got it working!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Will each number only be one digit? If so, we can use the LEFT Function, the RIGHT Function, and the MID Function (depending on what value we're bringing back) to extract the number.
For example, for the first number on the left, you can use:
=LEFT(QCEVCODE@row, 1)
Then for the second number in your second column:
=MID(QCEVCODE@row, 4, 1)
and so on. Then you can make it a column formula. Does that make sense? Let me know if it would be helpful to see a screen capture.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Genevieve P. !
Unfortunately not, there might be some case where there are more than 1 digit i.e "1,23,2,0,345"
In any case i have decided to insert this code in 5 separated columns for each type so the issue is sorted so far using this shortcut,
Thanks anyway, your idea was very sharp,
Regards,
Alberto
-
Ah, yes, thank you for clarifying. In this case, entering the values into separate columns makes sense and will be the best solution for now. I'm glad you got it working!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Can you please share how you did your formula. I have a similar challenge.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!