I have a spreadsheet with contacts in column A, and various tags in column B. I need to have each tag in a separate column, so instead of a column B for "Tags" I would have a column for "TagA" then one for "TagB" and so on.
How can you split a column in Google Sheets or Excel based on the contents of the cell?
It takes several steps, but it is possible to split different items from one column into separate, individual columns. Here’s how to do it.
Say you have a spreadsheet like the one above, with a column A with a name, and column B with a tag. Here,
Bob is tagged with both
banana, in two rows that each show one name and one tag. When we’re done, we want one row with
apple tag, and the
banana tag each in their own column.
To do this, we need to add each unique value from our original tag column to new, individual columns, then we want to match values from that first column with the new columns, and finally we want to merge all the rows to have only one name, each with all of its tags in separate columns. Here’s how to do that:
=if((C$1=$B2),C$1,""). There, we’re saying “If the header value is equal to the value in this row from our original column, then print the header value here; otherwise, put nothing”. Add the formula in the first new column and make sure it works, then paste it in all of the new cells under your new columns.
Your spreadsheet should now look something like this screenshot, with each unique value from your original column in unique columns—much like the results of a split text based on a comma, only here we split unique items from a column:
Now, if you want to merge the rows to say only have one row with
Bob and all his tags, you would need to install the Power Tools add-on. Delete the original Tag column, then select all the rest of the spreadsheet, click Addons -> Power tools -> Merge & Combine, and follow its instructions to merge all of your rows.
Beyond the team software your company pays for, which software do you subscribe to—from web-based SaaS to mobile app subscriptions? Anything you'd be the most sad to give up—or that you'd unsubscr...
Email apps come and go so fast—which one has kept you using it the longest, and why?