Use of Split

S

Steen

Hi

Hope for some help to a small problem. I have a string that contains a
headline and a description which are separated by a vbLf. I want to
separarate these into two separate strings. The problem is that the
Description also includes vbLf's.

I have tried the following but this only give me the first line of the
description:
frmTaskList.txtHeadLine.Value = Split(Target.Offset(0, 8), vbLf)(0)
frmTaskList.txtDescription.Value = Split(Target.Offset(0, 8), vbLf)(1)

Any help?

/Steen
 
R

Rick Rothstein

If you have multiple Line Feeds in a row, then Split will produce empty
string values for the array element produced for them. I suspect that may be
the case for your data... that there are two Line Feeds between your
headline and its description. It is easy enough to find out by putting this
line of code....

MsgBox InStr(Target.Offset(0, 8).Value, vbLf & vbLf)

in your program and if a number other than zero is display, then that is
your situation. If you do get a non-zero number, then the next thing to do
is determine whether there is always a double Line Feed after the headline
or whether they occur only some of the time. If always, then use an index
value of 2 instead of the 1 you now use. If sometimes, then you need to
remove it...

With frmTaskList
.txtHeadLine.Value = Split(Replace(Target.Offset(0, 8).Value, _
vbLf & vbLf, vbLf), vbLf)(0)
.txtDescription.Value = Split(Replace(Target.Offset(0, 8).Value, _
vbLf & vbLf, vbLf), vbLf)(0)
End With
 
K

keiji kounoike

Steen said:
Hi

Hope for some help to a small problem. I have a string that contains a
headline and a description which are separated by a vbLf. I want to
separarate these into two separate strings. The problem is that the
Description also includes vbLf's.

I have tried the following but this only give me the first line of the
description:
frmTaskList.txtHeadLine.Value = Split(Target.Offset(0, 8), vbLf)(0)
frmTaskList.txtDescription.Value = Split(Target.Offset(0, 8), vbLf)(1)

Any help?

/Steen
Click to expand...

As Rick said, if you have multiple Line feed's and can't determine how
many they are. Something like this might do

tmp = Split(Target.Offset(0, 8), vbLf)
i = 0
j = 0
Do While (j = 0)
Do While (tmp(i) = "")
i = i + 1
Loop
frmTaskList.txtHeadLine.Value = tmp(i)
j = 1
Loop
frmTaskList.txtDescription.Value = tmp(i)

keiji
 
R

Rick Rothstein

Another way to handle an unknown multiple number of Line Feeds...

TargetValue = Replace(WorksheetFunction.Trim(Replace(Replace(Target. _
Offset(0, 8), " ", Chr(1)), vbLf, " ")), Chr(1), " ")
frmTaskList.txtHeadLine.Value = Split(TargetValue, vbLf)(0)
frmTaskList.txtDescription.Value = Split(TargetValue, vbLf)(1)
 
R

Ron Rosenfeld

Hi

Hope for some help to a small problem. I have a string that contains a
headline and a description which are separated by a vbLf. I want to
separarate these into two separate strings. The problem is that the
Description also includes vbLf's.

I have tried the following but this only give me the first line of the
description:
frmTaskList.txtHeadLine.Value = Split(Target.Offset(0, 8), vbLf)(0)
frmTaskList.txtDescription.Value = Split(Target.Offset(0, 8), vbLf)(1)

Any help?

/Steen
Click to expand...

Another approach using regular expressions:

==========================================
Option Explicit

'------------------------------


frmTaskList.txtHeadLine.Value = SplitHL_Descr(Target.Offset(0, 8))

frmTaskList.txtDescription.Value = _
SplitHL_Descr(Target.Offset(0, 8), True)



'---------------------------------------
Option Explicit
Function SplitHL_Descr(str As String, _
Optional Descr As Boolean = False) As String
Dim re As Object, mc As Object
Dim i As Long

Set re = CreateObject("vbscript.regexp")
re.MultiLine = True
re.Pattern = "(.*$)([\S\s]*)"

If re.test(str) = True Then
Set mc = re.Execute(str)
If Descr = False Then
SplitHL_Descr = mc(0).submatches(0)
Else
SplitHL_Descr = mc(0).submatches(1)
End If
End If
End Function
===========================
--ron
 
K

keiji kounoike

Rick said:
Another way to handle an unknown multiple number of Line Feeds...

TargetValue = Replace(WorksheetFunction.Trim(Replace(Replace(Target. _
Offset(0, 8), " ", Chr(1)), vbLf, " ")), Chr(1), " ")
frmTaskList.txtHeadLine.Value = Split(TargetValue, vbLf)(0)
frmTaskList.txtDescription.Value = Split(TargetValue, vbLf)(1)
Click to expand...

Hi Rick
I may be a bit dense, but i can't get what your formula is doing.
I think it seems like a typo. I might be wrong, but Is this what you mean?

TargetValue =
Replace(Replace(WorksheetFunction.Trim(Replace(Replace(Target. _
Offset(0, 8), " ", Chr(1)), vbLf, " ")), " ", vbLf), _
Chr(1), " ")

keiji
 
R

Rick Rothstein

Another way to handle an unknown multiple number of Line Feeds...
I may be a bit dense, but i can't get what your formula is doing.
I think it seems like a typo. I might be wrong, but Is this what you mean?

TargetValue =
Replace(Replace(WorksheetFunction.Trim(Replace(Replace(Target. _
Offset(0, 8), " ", Chr(1)), vbLf, " ")), " ", vbLf), _
Chr(1), " ")
Click to expand...

No, you are not dense... I accidentally left out the step you provided in
your posting (namely, for those out there following this, converting the now
single space to a Line Feed before converting the Chr(1) characters back to
their original spaces). Thanks for catching that.
 
K

keiji kounoike

Rick said:
No, you are not dense... I accidentally left out the step you provided
in your posting (namely, for those out there following this, converting
the now single space to a Line Feed before converting the Chr(1)
characters back to their original spaces). Thanks for catching that.
Click to expand...

I'm from Japan and there is a proverb like this in Japan. In Japnese,
that is "Saru mo ki kara ochiru". translating it into English with words
to words, it would be "Even a monkey falls from trees". Equivalent to
this in English might be "Even Homer sometimes nods." but i'm not sure
whether it is right or wrong, beacause i'm not so good at English.

keiji
 
Top