PDA

View Full Version : Solved: split cell based on a multiple delimiters list



joawhzr
04-28-2009, 09:29 PM
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
04-28-2009, 10:59 PM
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
04-29-2009, 07:24 PM
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
04-29-2009, 09:50 PM
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
04-30-2009, 02:41 PM
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