Break down a column of multiple strings per cell into unqiue single cell strings

0
votes

I have a column of data pulled from a database that I need to sort out into graphs and visual data. The column can contain one or more different strings in each cell, but I need to pick out the unique strings. The strings are separated by a semicolon and contain letters, numbers and spaces.

Example:

  • ABC-23 Up;BCD-1 Hi;CDE-5
  • CDE-5
  • ABC-23 Up;HJK-3

I want to end up in a column of:

  • ABC-23 Up
  • BCD-1 Hi
  • CDE-5
  • HJK-3

Currently I use the excel function "text to columns" to split apart the column into however many cells I need based on the ';' as the split, and then do an advanced filter to remove duplicates but I was hoping there is a more automated way in case someone else has to pull the same information. Any ideas?

1
excel
To confirm - do you want duplicates to be removed? Also, are you familiar with VBA? Are you familiar with Excel's string comparison functions? (Such as Search / Mid) - Grade 'Eh' Bacon
Yes I want duplicates to be removed, and I am familiar with VBA and the comparison functions. Its just the combining them appropriately part in an efficient manner that is eluding me so far. - James Ski

1 Answers

0
votes

Sounds like my post here may be of use (assuming you're ok with a non-VBA solution, that is):

http://excelxor.com/2014/09/30/list-of-unique-entries-from-column-of-space-separated-strings/

Obviously you'd make amendments where appropriate to the delimiter being referenced, e.g. space to semi-colon.

Regards