Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) 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(Rep lace(Target. _ Offset(0, 8), " ", Chr(1)), vbLf, " ")), " ", vbLf), _ Chr(1), " ") keiji |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split up | New Users to Excel | |||
How do I remove split a split window? | New Users to Excel | |||
2nd split | Excel Discussion (Misc queries) | |||
Split a Value | Excel Discussion (Misc queries) | |||
h2 (30), c1(33)--split- c1(30) -d1(3) | Excel Programming |