Please Help Splitting data

[gwoodby]

MyVariable = Trim(Mid(data, (InStr(data, ",") - 1)))\

This is the string in wich im trying to Take the date out of,
secondname,thirdname,Mother:1,childname,10/12/2007,

However sometimes it might look like this depending on user input
secondname,thirdname,Mother:1,childname,10/12/2007,10/14/2007

i need Txtbox 1 = the first date, and TxtBox 2 = SecondDate

Here is The Function as is, I asked last week on here but am still
having trouble changing it.

Private Function getfields(data As String, intchoose As Integer) As
String
Dim firstdate As String
Dim seconddate As String
Dim vntX As Variant

vntX = Split(data, ",")

If intchoose = 0 Then
getfields = vntX(0) & "," & vntX(1) ' First Name Last Name
ElseIf intchoose = 2 Then
getfields = vntX(2) ' TxtBox6
ElseIf intchoose = 1 Then
TxtFirstName = vntX(0) ' TxtBox1
TxtLastName = vntX(1) ' TxtBox2
CBOMoFa = vntX(2) ' TxtBox6
TxtChild = vntX(3) ' TxtBox4
End If
End Function
TxtBox 3 and 5 are the dates Txt3 is date 1 Txt5 is date 2

 

[Smallweed]

Before struggling any further, have you tried Excel's excellent Data, Text to
Columns feature? Make sure you choose Delimited and comma as the delimiter,
then specify the date columns as MDY. You could record it as a macro.

 

[gwoodby]

Before struggling any further, have you tried Excel's excellent Data, Text to
Columns feature? Make sure you choose Delimited and comma as the delimiter,
then specify the date columns as MDY. You could record it as a macro.












- Show quoted text -

I have not but really i was trying to do it this way for future
reference and hopefully some understanding

 

[gwoodby]

I have not but really i was trying to do it this way for future
reference and hopefully some understanding - Hide quoted text -

- Show quoted text -

Can this be done this way or would i need to do it a different way?

 

[Ron Rosenfeld]

MyVariable = Trim(Mid(data, (InStr(data, ",") - 1)))\

This is the string in wich im trying to Take the date out of,
secondname,thirdname,Mother:1,childname,10/12/2007,

However sometimes it might look like this depending on user input
secondname,thirdname,Mother:1,childname,10/12/2007,10/14/2007

i need Txtbox 1 = the first date, and TxtBox 2 = SecondDate

Here is The Function as is, I asked last week on here but am still
having trouble changing it.

Private Function getfields(data As String, intchoose As Integer) As
String
Dim firstdate As String
Dim seconddate As String
Dim vntX As Variant

vntX = Split(data, ",")

If intchoose = 0 Then
getfields = vntX(0) & "," & vntX(1) ' First Name Last Name
ElseIf intchoose = 2 Then
getfields = vntX(2) ' TxtBox6
ElseIf intchoose = 1 Then
TxtFirstName = vntX(0) ' TxtBox1
TxtLastName = vntX(1) ' TxtBox2
CBOMoFa = vntX(2) ' TxtBox6
TxtChild = vntX(3) ' TxtBox4
End If
End Function
TxtBox 3 and 5 are the dates Txt3 is date 1 Txt5 is date 2

Here is a function that should extract the dates from your string and return it
in an array.

You can check on the number of dates by checking the number of elements in the
array (UBOUND), and pop them into your text fields.

----------------------------------------------
Option Explicit
Function GetDates(str As String)
Dim re As Object, mc As Object, m As Object
Dim temp()
Dim i As Long
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d{1,2}/\d{1,2}/\d{1,4}"
Set mc = re.Execute(str)
ReDim temp(0 To mc.Count - 1)
For Each m In mc
temp(i) = m
i = i + 1
Next m
GetDates = temp
End Function
-----------------------------------

As written the routine looks for a date "pattern" which I defined as being

one or two digits
followed by a "/"
followed by one or two digits
followed by a "/"
followed by 2 to four digits.

YOu could tighten that up depending on the variability in your date formats.
--ron

 

[Rick Rothstein \(MVP - VB\)]

See the inline code addition and my comment at the end...

MyVariable = Trim(Mid(data, (InStr(data, ",") - 1)))\

This is the string in wich im trying to Take the date out of,
secondname,thirdname,Mother:1,childname,10/12/2007,

However sometimes it might look like this depending on user input
secondname,thirdname,Mother:1,childname,10/12/2007,10/14/2007

i need Txtbox 1 = the first date, and TxtBox 2 = SecondDate

Here is The Function as is, I asked last week on here but am still
having trouble changing it.

Private Function getfields(data As String, intchoose As Integer) As
String
Dim firstdate As String
Dim seconddate As String
Dim vntX As Variant

vntX = Split(data, ",")

If intchoose = 0 Then
getfields = vntX(0) & "," & vntX(1) ' First Name Last Name
ElseIf intchoose = 2 Then
getfields = vntX(2) ' TxtBox6
ElseIf intchoose = 1 Then
TxtFirstName = vntX(0) ' TxtBox1
TxtLastName = vntX(1) ' TxtBox2
CBOMoFa = vntX(2) ' TxtBox6
TxtChild = vntX(3) ' TxtBox4

Txt3 = vntX(4)
Txt5 = vntX(5)

End If
End Function
TxtBox 3 and 5 are the dates Txt3 is date 1 Txt5 is date 2

Note that the the assignment to Txt5 assumes the trailing comma is present
(as shown in your sample data) when there is only one included date.

Rick

 

[Ron Rosenfeld]

See the inline code addition and my comment at the end...


Txt3 = vntX(4)
Txt5 = vntX(5)


Note that the the assignment to Txt5 assumes the trailing comma is present
(as shown in your sample data) when there is only one included date.

Rick

Much simpler than mine. I didn't read the OP's code carefully before
fabricating my recommendation.
--ron

 

[Rick Rothstein \(MVP - VB\)]

Much simpler than mine. I didn't read the OP's code carefully before
fabricating my recommendation.
--ron

The "hammer/nail" thing again, huh? <vbg>

Rick