lneidorf
08-11-2015, 02:19 PM
Hi there.
I've got an awful weekly work task that could benefit from a coded approach.
I receive a database export consisting of 50,000+ rows and many columns. There are four columns, each of which contain multivalue entries, separated by semicolons. My task is to pick apart the multiple values for each column and combine them in a string withthe corresponding entries in the other multi-value columns. An example would be helpful:
LOCATION
STORE NAME
FOLDER NAME
TITLE
RESULT
\\NetworkPrefix\Location1;\\NetworkPrefix\Location2
StoreName1.pst; StoreName2.pst
Folder/Name/1;Folder/Name/2
Document Title 1;Document Title 2
\\Location1\StoreName1.pst\FolderName1\Document Title 1;\\Location2\ StoreName2.pst\FolderName2\Document Title 2
In my sample, each of four columns in Row 1 contains two entries, separated by a semicolon. I want to parse out the first entry in each column, and combine them together separated by a backslash. The result should look like the entry in the final column of my table above, "RESULT" (cell E1).
I have greatly simplified my sample. In reality some rows contain as many as a dozen multivalue entries, always separated by semicolons. So I need some code or a UDF utilizing RegEx to parse and recombine the columns into a resulting single column.
I'm thinking a RegEx UDF would be ideal as it would allow me to tinker with the patterns to be matched. I've tried my hand at this, but have failed miserable. I'm afraid both my VBA skills and my RegEx skills are quite limited.
Thanks!
I've got an awful weekly work task that could benefit from a coded approach.
I receive a database export consisting of 50,000+ rows and many columns. There are four columns, each of which contain multivalue entries, separated by semicolons. My task is to pick apart the multiple values for each column and combine them in a string withthe corresponding entries in the other multi-value columns. An example would be helpful:
LOCATION
STORE NAME
FOLDER NAME
TITLE
RESULT
\\NetworkPrefix\Location1;\\NetworkPrefix\Location2
StoreName1.pst; StoreName2.pst
Folder/Name/1;Folder/Name/2
Document Title 1;Document Title 2
\\Location1\StoreName1.pst\FolderName1\Document Title 1;\\Location2\ StoreName2.pst\FolderName2\Document Title 2
In my sample, each of four columns in Row 1 contains two entries, separated by a semicolon. I want to parse out the first entry in each column, and combine them together separated by a backslash. The result should look like the entry in the final column of my table above, "RESULT" (cell E1).
I have greatly simplified my sample. In reality some rows contain as many as a dozen multivalue entries, always separated by semicolons. So I need some code or a UDF utilizing RegEx to parse and recombine the columns into a resulting single column.
I'm thinking a RegEx UDF would be ideal as it would allow me to tinker with the patterns to be matched. I've tried my hand at this, but have failed miserable. I'm afraid both my VBA skills and my RegEx skills are quite limited.
Thanks!