ExcelBanter - Use of Split

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Use of Split (https://www.excelbanter.com/excel-programming/418693-use-split.html)

Steen

Use of Split

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

Stefi

Use of Split

Try this way:

firstvblf = InStr(1, Target.Offset(0, 8), vbLf)
frmTaskList.txtHeadLine.Value = Left(Target.Offset(0, 8), firstvblf - 1)
frmTaskList.txtDescription.Value = Mid(Target.Offset(0, 8), firstvblf + 1)

Regards,
Stefi

€žSteen€ ezt Ã*rta:

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

Ron Rosenfeld

Use of Split

On Sat, 18 Oct 2008 00:37:01 -0700, Steen
wrote:

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

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

Steen

Use of Split

Hi Stefi

Thaks for this simple and effective answer - works great. :-)

"Stefi" wrote:

Try this way:

firstvblf = InStr(1, Target.Offset(0, 8), vbLf)
frmTaskList.txtHeadLine.Value = Left(Target.Offset(0, 8), firstvblf - 1)
frmTaskList.txtDescription.Value = Mid(Target.Offset(0, 8), firstvblf + 1)

Regards,
Stefi

€žSteen€ ezt Ã*rta:

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

keiji kounoike

Use of Split

Rick Rothstein

Use of Split

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

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

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(Rep lace(Target. _
Offset(0, 8), " ", Chr(1)), vbLf, " ")), " ", vbLf), _
Chr(1), " ")

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.

--
Rick (MVP - Excel)

keiji kounoike

Use of Split

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

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

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(Rep lace(Target. _
Offset(0, 8), " ", Chr(1)), vbLf, " ")), " ", vbLf), _
Chr(1), " ")

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.

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

All times are GMT +1. The time now is 01:52 AM.