Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 30th, 2011, 10:05 PM
Registered User
 
Join Date: Apr 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Import text with more than one delimiters

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?
 
Old March 31st, 2011, 06:43 AM
Authorized User
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

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.
 
Old March 31st, 2011, 08:54 PM
Registered User
 
Join Date: Apr 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old April 1st, 2011, 04:43 AM
Authorized User
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

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.
 
Old April 1st, 2011, 07:02 AM
Registered User
 
Join Date: Apr 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 :)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Text Import- How to import the first 5 lines. nickzhang.zn Excel VBA 1 August 4th, 2007 05:06 PM
How to import data from text file? starnet SQL Server 2000 1 September 18th, 2006 06:56 AM
Excel IMport Text Help!! money Excel VBA 2 April 30th, 2004 12:43 AM
Excel text Import and VB Franck Excel VBA 0 January 8th, 2004 09:57 AM
Text import - an extra bit Morris Access 6 August 11th, 2003 12:51 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.