How do I split a single cell into multiple rows?

Question

I am trying to create a project database that is able to filter based on the names of people who participated in the project. A single project is completed by more than one person but I am not able to filter to see the entire projects a single person has worked on. That is why I am considering splitting the cells categorized under the 'staff name' column into multiple rows.

Answer 1

Hi Ngozi_324,

Thanks for your question. I have a solution that can hopefully solve the problem you have without requiring the splitting of cells and that you can maintain the original data in your database.

First, highlight your data, click on the Data tab, then click Filter. This is assuming your data has column headers.

.

Next, go to the column header which has the people who worked on the projects and click on the little carrot. Click on Text Filters then click on Contains.

.

In the textbox on the right of "contains", type in the person you are trying to find.

.

Your data should be filtered to the specific person you are trying to find.

.

I hope this works for you. Let me know if it does, or if you have additional questions. Thanks.

Answer 2

Hi,

can you show us a sample data (dummy)?

Answer 3

Hi,

In the Query Editor, you may use Data > Split Column > Delimiter. Expand Advanced Options and select Rows. Click on OK. Click on Close and Load.

Answer 4

Do you mean to achieve this?

https://b23.tv/2NG26F0

//select * from split_then_transform limit 20;

cli_split_data~split_then_transform~,~ROLES;

select NAME,EMAIL,ROLES name from split_then_transformsplit;

source and expected result:

NAME	EMAIL	ROLES
John Doe	email address removed for privacy reasons	role1,role3
Jane Doe	email address removed for privacy reasons	role2,role4
Jim Doe	email address removed for privacy reasons	role4
John Smith	email address removed for privacy reasons	role1

NAME	EMAIL	name
John Doe	email address removed for privacy reasons	role1
John Doe	email address removed for privacy reasons	role3
Jane Doe	email address removed for privacy reasons	role2
Jane Doe	email address removed for privacy reasons	role4
Jim Doe	email address removed for privacy reasons	role4
John Smith	email address removed for privacy reasons	role1

Answer 5

This is very helpful, thank you so much! But is there any way to give the illusion that the outputted filtered information is in its own row? For instance, is it possible that only 'Georgia' appears without being followed by a comma separated list?