Extracting delimited values in Cells into a single value per cell column?

[superlative]

Hi there,

I know the brute force way of doing this, I'm looking for an elegant way of doing this.

Problem:

Column A has semi-colon delimited values:

John;Jane;Jack
Jonah;John;Jane
Jeff;Jill;Jack

I want to extract these values and put it into another worksheet with only a single value in each cell, and all values unique:

John
Jane
Jack
Jonah
Jeff
Jill

Now of course, I could go row by row and just use text functions to extract the names one by one, dump them (with duplicates) into a column, then use a filter function to remove the duplicates - but this is a "brute force" method and I was wondering if there was a faster more elegant way?

For example, there is a TEXTJOIN function, that joins values in cells into a comma delimited text. Is there an opposite function of TEXTJOIN, where given a delimiter it will divide into individual values?

Thanks!!

[Marc L]

Hi !

Different ways like using a worksheet function and a dynamic array or a Collection or a Dictionary or …

[NickyC]

Hi
you could use a macro
this once lists all names in the selected cells into sheet 2 start at cell A1

Please Login or Register  to view this content.

[superlative]

Hi there,

I found out that the function I'm looking for is:
objRange1.TextToColumns _
Destination:=Range("B1"), _
DataType:=xlDelimited, _
Tab:=False, _
Semicolon:=True, _
Comma:=False, _
Space:=False, _
Other:=False, _
OtherChar:="-"

This will split all the values into individual cells in different columns (if 3 delimited values, will create 3 columns). However, I can merge all the columns by appending the columns to one another and then using a filter to remove duplicates. I think that's the fastest way??

Thanks!

[Marc L]

So you choose the brute force … Fast enough as it's Excel inner features.

[jindon]

Try

Please Login or Register  to view this content.

[superlative]

After further testing, this is the code that works:

Please Login or Register  to view this content.

the previous code using TextToColumns is garbage. You cannot set the Destination in another worksheet - it insists on writing to the same worksheet, so it's useless. Frustrated.

[superlative]

Try

Please Login or Register  to view this content.

That code looks very elegant, but how do I add the output to another worksheet? Let's say we call the Worksheet "Results", what is the syntax? Thanks!

[superlative]

I got it!! This is the code (Thanks Everyone, especially Jindon for the help!):

Please Login or Register  to view this content.

Only thing though - the first cell in "Results" (A1) is blank. Anyway to avoid having a blank first value? Thanks!!!

[superlative]

I'm almost there... The code above works if the values start in cell A1, but how do I change the syntax so it reads from some column of worksheet "Input Values" ? Thanks!

[jindon]

1) change a1 to a2
2) change Sheets.Add to Sheets("results")

This is from phone so might have typo etc.

[superlative]

okay, this is the syntax I was looking for that sort of works:

x = Split(Join(Application.Transpose(SWS.Range(ColumnLet & FirstRow, SWS.Range(ColumnLet & Rows.Count).End(xlUp)).Value), ";"), ";")

It works for the time being, but I am not able to figure out the syntax for specifying the LastRow in the range. I am forced to use Rows.Count. If for example I set LastRow (in place of "Rows.Count")= 3, and there are no values in Row 4, then the code works. But if there is a value like "Frank" in row 4, then the code fails.

So in other words, so long as I make sure that the Column ONLY contains values I want, then the code above works. But if I want to specify the LastRow of the range I want, I can't figure out how to do it. Thanks!

[superlative]

Okay got it: this is the syntax that works!:
x = Split(Join(Application.Transpose(SWS.Range(ColumnLet & FirstRow, ColumnLet & LastRow).Value), ";"), ";")

where SWS is the Worksheet we are copying from

[jindon]

OK, this just deal with the cells in Col.A that have value, ignores blanks.

Please Login or Register  to view this content.