On Mon, 3 Apr 2006 08:50:01 -0700, naulerich
wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text" and
"this is a test" columns therefore text to columns wont work. However what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

Having read a bunch of this thread, it seems to me that you may be able to
parse out your data using Regular Expressions in VBA.

To enter the code, <alt<F11 opens the VBA Editor. Ensure your project is
highlighted in the project explorer window, then Insert Module and paste the
code below into the window that opens. In the VB Editor, you must also select
Tools/References and select Microsoft VBScript Regular Expressions 5.5 from the
list.

To use this, with your data in A1, enter the following formula into B1 and
copy/drag across as far as required.

=remid($A1,"""[^""]+""",COLUMNS($B:B))

The Regular Expression portion: """[^""]+""" says generate a match that
begins with a double quote; is followed by any number of characters that do not
include a double quote; and terminated by a double quote.

The Columns function is merely a method of generating an increasing number as
you copy/drag the formula across several columns; and it will cause the
expression to return the 1st, 2nd, etc instance of the matched pattern.

The resultant string will have double quotes around it. If that is not
satisfactory, you can embed the above in a SUBSTITUTE function to get rid of
them.

=SUBSTITUTE(remid($A1,"""[^""]+""",COLUMNS($B:B)),"""","")

============================================
Option Explicit
Function REMid(str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True) _
As Variant 'Variant as value may be string or array

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection

Dim i As Long 'counter
Dim t() As String 'container for array results

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(str) ' Execute search.

On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim t(1 To UBound(Index))
For i = 1 To UBound(Index)
t(i) = colMatches(Index(i) - 1)
Next i
REMid = t()
Else
REMid = CStr(colMatches(Index - 1))
If IsEmpty(REMid) Then REMid = ""
End If
On Error GoTo 0 'reset error handler
Else
REMid = ""
End If
End Function
=============================

Let me know if this works for you.



--ron