Import text with more than one delimiters

ymeyaw · March 30th, 2011, 10:05 PM

Hi,

If i have more than one delimiters (with , | - " : ) in a text file, what built-in function in VBA excel that i can use to split them into each columns?

example:

abc|def:ghijklmn,123
edf|aaa:12345fd,555

How can i split the characters to every column?

disel2010 · March 31st, 2011, 06:43 AM

Hi,

does it need to be vba ?

With the Text to Columns function you can already split on multiple delimiters (but only tab, semicolon, comma, space and one other you can specify)..

If you have multiple delimiters that are not predefined in this Text to Column function, you can first use Find/replace to convert these in e.g. semicolon.

ymeyaw · March 31st, 2011, 08:54 PM

Hi Disel,

Yes. I had the whole excel vba codes and this part i need to do it using vba as well. Reason being, there are few txt files with more than 10,000 rows to input to my excel and i dont want to have too many manual steps to do it.

disel2010 · April 1st, 2011, 04:43 AM

Hi,

I know the feeling... Automate wherever possible, you should !

Although I'm afraid that if you have txt files with 10.000 lines you are bound to face some bad data (where the separators have been used as plain character)...
Not sure how you can prepare for bad data, but to do what you want in vba I suggest you to follow the same logic to first replace the characters and then split by the single character.
Something like this:

Code:

 
Range("A1:A2").Select
Selection.Replace What:=",", Replacement:=";"
Selection.Replace What:="|", Replacement:=";"
Selection.Replace What:="-", Replacement:=";"
Selection.Replace What:="""", Replacement:=";"
Selection.Replace What:=":", Replacement:=";"
Selection.TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, Semicolon:=True

I hope this helps.

ymeyaw · April 1st, 2011, 07:02 AM

Hi mate,

Thanks for your advise :) Initially i was thinking to use VB Reg_Exp but i think is too much for me :)

You are right, with replace delimiter from code and use Text to Column for last action will be the easiest and faster way for me to complete my assignment.

Thanks again :)