Split cell based on a multiple delimiters list

[joawhzr]

Hi my friends I've been surfing the web but I could not find what I need, so a post this thread.

I have a column with strings like:

AA anystring BB anystring CC anystring
BB anystring DD anystring
...

being "anystring" some multiword string

And a list of delimiters:
AA
BB
CC
DD
...

What I need is to split everytime a delimiter is found in a new column

AA anystring ---- BB anystring ---- CC anystring
BB anystring ---- DD anystring

I have found in this forum some code but it uses only one delimiter

I would appreciate any help

Thanks in advance

[mikerickson]

You could use this UDF and a helper column

If your column of XX,YY,... delimited strings are in A:A
and your column of delimiters is in B1:B10.
Put this in C1 and drag down

=MassReplace(A1,$B$1:$B$10,",")

then use Text to Columns with a comma delimiter.


Function MassReplace(inputString As String, fromRange As Range, toString As String) As String
Dim oneCell As Range
For Each oneCell In fromRange
inputString = Application.Substitute(inputString, CStr(oneCell), toString)
Next oneCell
MassReplace = inputString
End Function

[joawhzr]

Hi mikerickson, I tested the code and it works!, you made in o few lines of code that i'm almost ashamed to ask you, can I hold after the comma delimiter the delimiter string i'm using? :

I get

anystring1 , anystring2 , anystring3

coud get

AA anystring1 , BB anystring2 , CC anystring3

thanks in advance

jhr

[joawhzr]

I guess I've answered myself :

Function MassReplace(inputString As String, fromRange As Range, toString As String) As String
Dim oneCell As Range
For Each oneCell In fromRange
inputString = Application.Substitute(inputString, CStr(oneCell), toString + CStr(oneCell))
Next oneCell
MassReplace = inputString
End Function

this solution is the beginning of a normalization task I have, I guess I can mark as solved this thread and begin another with more doubts on this task

thanks a lot mike..

jhr

[mdmackillop]

Welcome to VBAX.
You seem to have this sorted.
If it helps for the future, you can post a workbook using Manage Attachments in the Go Advanced reply section
Regards
MD