No Problem. This version of Split works a little like Don's idea. It may not
be the most efficient code in the world but it works well - see the comment in
the code about changing the split character.
Ian
Public Function QSplit(aString As String, Optional sDelim As String = "") As
String()
' The normal VB Split function does not take into consideration a comma embedded
' within a quotted string and will split it incorrectly.
'
' This function also prunes all paired leading and trailing double quote marks
'
' Returns: Single-Dimension array, same result as the SPLIT Function.
'
' *****NOTE WELL****
' This routine assumes there will be no chr(8) (which is a Backspace) in the
data
' If there is one then the data will not be split correctly. To change this
amend the
' assignment to sSplitChar
'
Dim sText As String
Dim i As Long
Dim sQuotes As String
Dim aData1() As String
Dim bInQuotes As Boolean
Dim s As String
Dim sSplitChar As String
Dim myDelim As String
sQuotes = Chr(34)
sSplitChar = Chr(8) ' This is the character we split at - it MUST NOT
appear in the data
If Not Len(sDelim) = 0 Then
myDelim = sDelim
End If
If Len(myDelim) = 0 Then myDelim = ","
If InStr(aString, sQuotes) = 0 Then
aData1 = Split(aString, myDelim) ' Use standard Split if there is no
quotes in the string
Else
bInQuotes = False
sText = aString
For i = 1 To Len(sText)
s = Mid$(sText, i, 1)
If s = sQuotes Then
bInQuotes = Not bInQuotes
ElseIf s = myDelim Then
If bInQuotes = False Then Mid$(sText, i, 1) = sSplitChar
End If
Next
aData1 = Split(sText, sSplitChar)
End If
' The following loop removes all quotes around the string and trims any
spaces
' If you don't want this action then just remove the loop!
For i = 0 To UBound(aData1)
aData1(i) = Unquote(aData1(i))
Next
QSplit = aData1
End Function
Public Function Unquote(aString As String) As String
' Recursively removes all double quotes surrounding a string
'
Dim myString As String
myString = Trim$(aString)
If left$(myString, 1) = Chr(34) And right(myString, 1) = Chr(34) Then
myString = Unquote(Mid$(myString, 2, Len(myString) - 2))
End If
Unquote = myString
End Function
----- Original Message -----
From: HouseDad
To: [email protected]
Sent: Wednesday, February 23, 2005 8:18 AM
Subject: [vbhelp] Re: Re-inventing the Wheel
--- In [email protected], "Ian Brooke" <[EMAIL PROTECTED]> wrote:
> That depends what you mean. Splitting a line on commas is easy
(using standard Split). If you have commas in the string that you
don't want to split on but they are enclosed in quotes (for example
abc,"a,b","cde,fg" being only 3 fields) then that also is easy and I
have a routine that does that which you are welcome to.
>
> Ian
That would be great! I spent 6 hours yesterday fixing a file
that had commas in the middle of fields, as well as other stuff.
[EMAIL PROTECTED]
Also, I think I emailed you before on another topic, do you
recal whether you received it or not? Can't recall right now what it
was, been busy busy busy. My company hired someone else to do a web
site for one of our customers and it's causing ME problems trying to
fix all their mistakes. I mean, how hard can it be to design a
secure web site that accesses databases and allows users to modify
data online...
[C]
'// =======================================================
Rules : http://ReliableAnswers.com/List/Rules.asp
Home : http://groups.yahoo.com/group/vbHelp/
=======================================================
Post : [email protected]
Join : [EMAIL PROTECTED]
Leave : [EMAIL PROTECTED]
'// =======================================================
------------------------------------------------------------------------------
Yahoo! Groups Links
a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/vbhelp/
b.. To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
[Non-text portions of this message have been removed]
'// =======================================================
Rules : http://ReliableAnswers.com/List/Rules.asp
Home : http://groups.yahoo.com/group/vbHelp/
=======================================================
Post : [email protected]
Join : [EMAIL PROTECTED]
Leave : [EMAIL PROTECTED]
'// =======================================================
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/vbhelp/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
