-
Solved: split cell based on a multiple delimiters list
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
-
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.
[VBA]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
[/VBA]
-
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
-
I guess I've answered myself :
[VBA]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[/VBA]
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
-
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules